Test DB connection report from remote db

*&———————————————————————*
*& Report ADBC_DEMO *
*& *
*&———————————————————————*
*& This is a demo program that demonstrates the usage of the ADBC
*& API consisting of the classes CL_SQL_CONNECTION, CL_SQL_STATEMENT,
*& CL_SQL_PREPARED_STATEMENT and CL_SQL_RESULT_SET.
*&
*& The program creates a test table ‘ADBC_DEMO__’ and executes some
*& DML statemens and queries on that table. As its output the program
*& traces the execution of the ADBC methods and their results into
*& an ABAP list which will be displayed as the program’s result.
*&
*& Before the program finishes the test table is dropped again.
*&
*& The program may run against the application server’s default
*& connection or against any other database connection that must have
*& been defined in table DBCON before. This is controlled by the
*& input parameter CON_NAME. If this parameter is left initial then
*& the program runs against the default connection, otherwise it
*& tries to connect and execute on the connection specified.
*&
*&———————————————————————*

REPORT adbc_demo.

************************************************************************
* Parameters and Select Options
************************************************************************

PARAMETERS:
con_name TYPE dbcon-con_name.

************************************************************************
* Type definitions
************************************************************************

TYPES:
BEGIN OF adbc_demo_t,
col1 TYPE i,
col2(4) TYPE n,
END OF adbc_demo_t.

************************************************************************
* Global constants and variables
************************************************************************

CONSTANTS:
c_tabname TYPE string VALUE `ADBC_DEMO__`,
c_coldefs TYPE string VALUE `(COL1 integer, COL2 char(4))`.

DATA:
sqlerr_ref TYPE REF TO cx_sql_exception,
con_ref TYPE REF TO cl_sql_connection.

************************************************************************
START-OF-SELECTION.
************************************************************************

TRY.
PERFORM:
connect USING con_name con_ref,
create_table USING con_ref c_tabname c_coldefs,
insert_rows USING con_ref,
select_into_variables USING con_ref,
update_rows USING con_ref,
select_into_structure USING con_ref,
delete_rows USING con_ref,
select_into_table USING con_ref,
drop_table USING con_ref c_tabname,
disconnect USING con_ref.

CATCH cx_sql_exception INTO sqlerr_ref.
PERFORM handle_sql_exception USING sqlerr_ref.
ENDTRY.

************************************************************************
END-OF-SELECTION.
************************************************************************

************************************************************************
* Forms
************************************************************************

*———————————————————————-*
* FORM connect
*———————————————————————-*
* Connects to the database specified by the logical connection name
* P_CON_NAME which is expected to be specified in table DBCON. In case
* of success the form returns in P_CON_REF a reference to a connection
* object of class CL_SQL_CONNECTION.
*———————————————————————-*
* –> P_CON_NAME logical connection name
* <– P_CON_REF reference to a CL_SQL_CONNECTION object
*———————————————————————-*
FORM connect USING p_con_name TYPE dbcon-con_name
p_con_ref TYPE REF TO cl_sql_connection
RAISING cx_sql_exception.

* if CON_NAME is not initial then try to open the connection, otherwise
* create a connection object representing the default connection.
IF p_con_name IS INITIAL.
CREATE OBJECT p_con_ref.
ELSE.
PERFORM trace_0 USING ‘GET_CONNECTION’ p_con_name.
p_con_ref = cl_sql_connection=>get_connection( p_con_name ).
PERFORM trace_result USING p_con_name ‘opened’.
ENDIF.

ENDFORM. ” connect

*———————————————————————*
* FORM create_table
*———————————————————————*
* Creates or replaces the test table C_TABNAME with columns COL1
* (integer) and COL2 (CHAR(4)).
*———————————————————————*
FORM create_table
USING p_con_ref TYPE REF TO cl_sql_connection
p_tabname TYPE string
p_coldefs TYPE string
RAISING cx_sql_exception.

DATA:
l_sqlerr_ref TYPE REF TO cx_sql_exception,
l_stmt TYPE string,
l_stmt_ref TYPE REF TO cl_sql_statement.

* create a statement object
l_stmt_ref = p_con_ref->create_statement( ).

* create the statement string
CONCATENATE
‘create table’ p_tabname p_coldefs
INTO l_stmt SEPARATED BY space. “#EC NOTEXT

* execute the DDL command; catch the exception in order to handle the
* case if the table already exists
TRY.
PERFORM trace_0 USING ‘EXECUTE_DDL’ l_stmt.
l_stmt_ref->execute_ddl( l_stmt ).
CATCH cx_sql_exception INTO l_sqlerr_ref.
IF l_sqlerr_ref->dbobject_exists = ‘X’
OR l_sqlerr_ref->internal_error = 1024.
* table already exists => drop it and try it again
WRITE:
/ c_tabname,
‘already exists => drop table and try again’. “#EC NOTEXT
PERFORM:
drop_table USING p_con_ref p_tabname,
create_table USING p_con_ref p_tabname p_coldefs.
ELSE.
RAISE EXCEPTION l_sqlerr_ref.
ENDIF.
ENDTRY.

IF l_sqlerr_ref IS INITIAL.
PERFORM trace_result USING c_tabname ‘created’.
ENDIF.

ENDFORM. ” create_table

*———————————————————————*
* FORM disconnect
*———————————————————————*
* Disconnect from the given connection except for the default
* connection.
*———————————————————————*
FORM disconnect
USING p_con_ref TYPE REF TO cl_sql_connection
RAISING cx_sql_exception.

* if a non-default connection has been opened we close it now
IF p_con_ref->get_con_name( ) IS NOT INITIAL.
data: l_con_name type dbcon-con_name.

l_con_name = p_con_ref->get_con_name( ).
PERFORM trace_0 USING ‘CLOSE CONNECTION’ l_con_name.
p_con_ref->close( ).
PERFORM trace_result USING l_con_name ‘closed’.
ENDIF.

ENDFORM. “disconnect

*———————————————————————*
* FORM drop_table
*———————————————————————*
* Drops the test table C_TABNAME.
*———————————————————————*
FORM drop_table
USING p_con_ref TYPE REF TO cl_sql_connection
p_tabname TYPE string
RAISING cx_sql_exception.

DATA:
l_stmt TYPE string,
l_stmt_ref TYPE REF TO cl_sql_statement.

* create a statement object
l_stmt_ref = p_con_ref->create_statement( ).

* create the statement string
CONCATENATE
‘drop table’ p_tabname
INTO l_stmt SEPARATED BY space.

* execute the DDL command
PERFORM trace_0 USING ‘EXECUTE_DDL’ l_stmt.
l_stmt_ref->execute_ddl( l_stmt ).

PERFORM trace_result USING c_tabname ‘dropped’.

ENDFORM. “drop_table

*———————————————————————*
* FORM delete_rows
*———————————————————————*
* Deletes all rows from the test table C_TABNAME having COL2 = 1000.
*———————————————————————*
FORM delete_rows
USING p_con_ref TYPE REF TO cl_sql_connection
RAISING cx_sql_exception.

DATA:
l_stmt TYPE string,
l_stmt_ref TYPE REF TO cl_sql_statement,
l_dref TYPE REF TO data,
l_col2 TYPE adbc_demo_t-col2,
l_row_cnt TYPE i.

* create the statement string
CONCATENATE
‘delete from’ c_tabname ‘where COL2 = ?’
INTO l_stmt SEPARATED BY space. “#EC NOTEXT

* create a statement object
l_stmt_ref = p_con_ref->create_statement( ).

* bind input variable
GET REFERENCE OF l_col2 INTO l_dref.
l_stmt_ref->set_param( l_dref ).

* set input value and execute the statement
l_col2 = 1000.
PERFORM trace_1 USING ‘EXECUTE_UPDATE’ l_stmt l_col2.
l_row_cnt = l_stmt_ref->execute_update( l_stmt ).

PERFORM trace_result USING l_row_cnt ‘rows deleted’.

ENDFORM. “delete_rows

*———————————————————————*
* FORM handle_sql_exception
*———————————————————————*
* Write appropriate error messages when a SQL exception has occured
*———————————————————————*
* –> P_SQLERR_REF reference to a CX_SQL_EXCEPTION object
*———————————————————————*
FORM handle_sql_exception
USING p_sqlerr_ref TYPE REF TO cx_sql_exception.

FORMAT COLOR COL_NEGATIVE.
IF p_sqlerr_ref->db_error = ‘X’.
WRITE: / ‘SQL error occured:’, p_sqlerr_ref->sql_code,
/ p_sqlerr_ref->sql_message. “#EC NOTEXT
ELSE.
WRITE:
/ ‘Error from DBI (details in dev-trace):’,
p_sqlerr_ref->internal_error. “#EC NOTEXT
ENDIF.

ENDFORM. “handle_sql_exception

*———————————————————————*
* FORM insert_rows
*———————————————————————*
* Inserts 10 rows into the test table with columns values COL1 and
* COL2 set to SY-INDEX each where SY-INDEX runs from 1 to 10. Because
* the same (INSERT-)statement has to be executed 10 times (only
* differing in its input values) we make use of a prepared statement
* object (instance of CL_SQL_PREPARED_STATEMENT) and prepare the
* statement only once but execute it 10 times.
*———————————————————————*
FORM insert_rows
USING p_con_ref TYPE REF TO cl_sql_connection
RAISING cx_sql_exception.

DATA:
l_stmt TYPE string,
l_prepstmt_ref TYPE REF TO cl_sql_prepared_statement,
l_dref TYPE REF TO data,
l_col2 TYPE adbc_demo_t-col2,
l_wa TYPE adbc_demo_t.

* create the statement string
CONCATENATE
‘insert into’ c_tabname ‘values (?,?)’
INTO l_stmt SEPARATED BY space.

* create a prepared statement object
l_prepstmt_ref = p_con_ref->prepare_statement( l_stmt ).

* bind input variables
GET REFERENCE OF sy-index INTO l_dref.
l_prepstmt_ref->set_param( l_dref ).
GET REFERENCE OF l_col2 INTO l_dref.
l_prepstmt_ref->set_param( l_dref ).

* execute the statement 10 times, this means inserting 10 rows
DO 10 TIMES.
l_col2 = sy-index.
PERFORM trace_2 USING ‘EXECUTE_UPDATE’ l_stmt sy-index l_col2.
l_prepstmt_ref->execute_update( ).
ENDDO.
PERFORM trace_result USING 10 ‘rows inserted’.

* don’t forget to close the prepared statement in order to free
* resources on the database
l_prepstmt_ref->close( ).

ENDFORM. “insert_rows

*———————————————————————*
* FORM select_into_variables
*———————————————————————*
* Selects some rows from the test table and fetches the result rows
* columnwise in appropriate output variables.
*———————————————————————*
FORM select_into_variables
USING p_con_ref TYPE REF TO cl_sql_connection
RAISING cx_sql_exception.

DATA:
l_stmt TYPE string,
l_stmt_ref TYPE REF TO cl_sql_statement,
l_dref TYPE REF TO data,
l_res_ref TYPE REF TO cl_sql_result_set,
l_col1 TYPE adbc_demo_t-col1,
l_col2 TYPE adbc_demo_t-col2.

* create the query string
CONCATENATE
‘select * from’ c_tabname ‘where COL1 >= ?’
INTO l_stmt SEPARATED BY space. “#EC NOTEXT

* create a statement object
l_stmt_ref = p_con_ref->create_statement( ).

* bind input variable
GET REFERENCE OF l_col1 INTO l_dref.
l_stmt_ref->set_param( l_dref ).

* set the input value and execute the query
l_col1 = 6.
PERFORM trace_1 USING ‘EXECUTE_QUERY’ l_stmt l_col1.
l_res_ref = l_stmt_ref->execute_query( l_stmt ).

* set output variables
GET REFERENCE OF l_col1 INTO l_dref.
l_res_ref->set_param( l_dref ).
GET REFERENCE OF l_col2 INTO l_dref.
l_res_ref->set_param( l_dref ).

* loop over the result set and trace the output rows
WHILE l_res_ref->next( ) > 0.
PERFORM trace_next_vars USING l_col1 l_col2.
ENDWHILE.
PERFORM trace_result USING l_res_ref->rows_fetched
‘rows fetched’.

* don’t forget to close the result set object in order to free
* resources on the database
l_res_ref->close( ).

ENDFORM. “select_into_variables

*———————————————————————*
* FORM select_into_structure
*———————————————————————*
* Selects some rows from the test table and fetches the result rows
* in a structure that corresponds to the queries select list columns.
*———————————————————————*
FORM select_into_structure
USING p_con_ref TYPE REF TO cl_sql_connection
RAISING cx_sql_exception.

DATA:
l_stmt TYPE string,
l_stmt_ref TYPE REF TO cl_sql_statement,
l_dref TYPE REF TO data,
l_res_ref TYPE REF TO cl_sql_result_set,
l_col1 TYPE adbc_demo_t-col1,
l_wa TYPE adbc_demo_t.

* create the query string
CONCATENATE
‘select * from’ c_tabname ‘where COL1 > ?’
INTO l_stmt SEPARATED BY space. “#EC NOTEXT

* create a statement object
l_stmt_ref = p_con_ref->create_statement( ).

* bind input variable
GET REFERENCE OF l_col1 INTO l_dref.
l_stmt_ref->set_param( l_dref ).

* set the input value and execute the query
l_col1 = 5.
l_res_ref = l_stmt_ref->execute_query( l_stmt ).
PERFORM trace_1 USING ‘EXECUTE_QUERY’ l_stmt l_col1.

* set output structure
GET REFERENCE OF l_wa INTO l_dref.
l_res_ref->set_param_struct( l_dref ).

* loop over the result set and trace the output rows
WHILE l_res_ref->next( ) > 0.
PERFORM trace_next_struct USING l_wa.
ENDWHILE.
PERFORM trace_result USING l_res_ref->rows_fetched
‘rows fetched’.

* don’t forget to close the result set object in order to free
* resources on the database
l_res_ref->close( ).

ENDFORM. “select_into_structure

*———————————————————————*
* FORM select_into_table
*———————————————————————*
* Selects some rows from the test table and fetches the result rows
* into an internal table whose row structure corresponds to the
* queries select list columns.
*———————————————————————*
FORM select_into_table
USING p_con_ref TYPE REF TO cl_sql_connection
RAISING cx_sql_exception.

DATA:
l_stmt TYPE string,
l_stmt_ref TYPE REF TO cl_sql_statement,
l_dref TYPE REF TO data,
l_res_ref TYPE REF TO cl_sql_result_set,
l_col1 TYPE adbc_demo_t-col1,
l_col2 TYPE adbc_demo_t-col2,
l_itab TYPE STANDARD TABLE OF adbc_demo_t,
l_row_cnt TYPE i.

* create the query string
CONCATENATE
‘select * from’ c_tabname ‘where COL1 <= ? and COL2 >= ?’
INTO l_stmt SEPARATED BY space. “#EC NOTEXT

* create a statement object
l_stmt_ref = p_con_ref->create_statement( ).

* bind input variables
GET REFERENCE OF l_col1 INTO l_dref.
l_stmt_ref->set_param( l_dref ).
GET REFERENCE OF l_col2 INTO l_dref.
l_stmt_ref->set_param( l_dref ).

* set the input values and execute the query
l_col1 = 7.
l_col2 = 1.
PERFORM trace_2 USING ‘EXECUTE_QUERY’ l_stmt l_col1 l_col2.
l_res_ref = l_stmt_ref->execute_query( l_stmt ).

* set output table
GET REFERENCE OF l_itab INTO l_dref.
l_res_ref->set_param_table( l_dref ).

* get the complete result set
l_row_cnt = l_res_ref->next_package( ).

* display the contents of the output table
PERFORM trace_next_package USING l_itab.
PERFORM trace_result USING l_row_cnt ‘rows fetched’.

* don’t forget to close the result set object in order to free
* resources on the database
l_res_ref->close( ).

ENDFORM. “select_into_table

*———————————————————————*
* FORM trace_0
*———————————————————————*
* Trace the execution of a statement without input variables.
*———————————————————————*
FORM trace_0
USING p_method TYPE c
p_stmt.

WRITE:
/ p_method COLOR COL_KEY, p_stmt COLOR COL_NORMAL. “#EC NOTEXT

ENDFORM. “trace_0

*———————————————————————*
* FORM trace_1
*———————————————————————*
* Trace the execution of a statement with one input variable.
*———————————————————————*
FORM trace_1
USING p_method TYPE c
p_stmt TYPE string
p_v1.

PERFORM trace_0 USING p_method p_stmt.
WRITE: ‘<== (?1 =’, p_v1 NO-GAP, ‘)’. “#EC NOTEXT

ENDFORM. “trace_1

 

*———————————————————————*
* FORM trace_2
*———————————————————————*
* Trace the execution of a statement with two input variables
*———————————————————————*
FORM trace_2
USING p_method TYPE c
p_stmt TYPE string
p_v1
p_v2.

PERFORM trace_0 USING p_method p_stmt.
WRITE: ‘<== (?1 =’, p_v1, ‘, ?2 =’, p_v2 NO-GAP, ‘)’. “#EC NOTEXT

ENDFORM. “trace_2

*———————————————————————*
* FORM trace_next_vars
*———————————————————————*
* Trace the execution of a NEXT method together with the values
* fetched.
*———————————————————————*
FORM trace_next_vars
USING p_v1 TYPE adbc_demo_t-col1
p_v2 TYPE adbc_demo_t-col2.

WRITE: / ‘NEXT’ COLOR COL_KEY,
‘==> {‘ NO-GAP, (3) p_v1 NO-GAP, ‘,’,
p_v2 NO-GAP, ‘}’. “#EC NOTEXT

ENDFORM. “trace_next_vars

*———————————————————————*
* FORM trace_next_struct
*———————————————————————*
* Trace the execution of a NEXT method together with the output
* workarea where the result row was fetched
*———————————————————————*
FORM trace_next_struct
USING p_struct TYPE adbc_demo_t.

WRITE: / ‘NEXT’ COLOR COL_KEY,
‘==> {‘ NO-GAP, (3) p_struct-col1 NO-GAP, ‘,’,
p_struct-col2 NO-GAP, ‘}’. “#EC NOTEXT

ENDFORM. “trace_next_struct

 

*———————————————————————*
* FORM trace_next_package
*———————————————————————*
* Trace the execution of a NEXT_PACKAGE method together with
* the contents of the internal table where the result were returned.
*———————————————————————*
FORM trace_next_package
USING p_itab TYPE table.

FIELD-SYMBOLS:
<l_line> TYPE adbc_demo_t.

WRITE:
/ ‘NEXT_PACKAGE’ COLOR COL_KEY,
‘==> {‘ . “#EC NOTEXT
LOOP AT p_itab ASSIGNING <l_line>.
IF sy-tabix > 1.
NEW-LINE.
POSITION 20.
ENDIF.
WRITE:
‘{‘ NO-GAP, (3) <l_line>-col1 NO-GAP, ‘,’,
<l_line>-col2 NO-GAP, ‘}’. “#EC NOTEXT
ENDLOOP.
WRITE: ‘}’.
ENDFORM. “trace_next_package

*———————————————————————*
* FORM trace_result
*———————————————————————*
* Trace a summary for a statement execution.
*———————————————————————*
FORM trace_result USING p_1
p_2.

FORMAT COLOR COL_TOTAL.
WRITE: / ‘==>’, p_1, p_2.
FORMAT COLOR OFF.
ULINE.
ENDFORM. “trace_result

*———————————————————————*
* FORM update_rows
*———————————————————————*
* Updates COL2 for some rows of the test table.
*———————————————————————*
FORM update_rows
USING p_con_ref TYPE REF TO cl_sql_connection
RAISING cx_sql_exception.

DATA:
l_stmt TYPE string,
l_stmt_ref TYPE REF TO cl_sql_statement,
l_dref TYPE REF TO data,
l_col1 TYPE adbc_demo_t-col1,
l_col2 TYPE adbc_demo_t-col2,
l_row_cnt TYPE i.

* create the statement string
CONCATENATE
‘update’ c_tabname ‘set COL2 = ? where COL1 >= ?’
INTO l_stmt SEPARATED BY space. “#EC NOTEXT

* create a prepared statement object
l_stmt_ref = p_con_ref->create_statement( ).

* bind input variables
GET REFERENCE OF l_col2 INTO l_dref.
l_stmt_ref->set_param( l_dref ).
GET REFERENCE OF l_col1 INTO l_dref.
l_stmt_ref->set_param( l_dref ).

* set input values and execute the statement
l_col1 = 8.
l_col2 = 1000.
PERFORM trace_2 USING ‘EXECUTE_UPDATE’ l_stmt l_col2 l_col1.
l_row_cnt = l_stmt_ref->execute_update( l_stmt ).

PERFORM trace_result USING l_row_cnt ‘rows updated’.

ENDFORM. “update_rows

Barcode Abap

 

Introduction

With traditional bar code printing, the bar codes are generated at the printer and not in the SAP system. Special hardware, such as SIMM modules, is required to do this. If the bar code is to be formatted or controlled with traditional bar code printing, print controls must be changed in the printer commands. In contrast to traditional bar code printing, the new bar code printing does not require any special software, since the bar code is sent to the printer as a graphic. However, the new bar code printing only works with Smart Forms.

1. Maintaining the Font(s)

  • Go to TCODE SE73 and display the system bar codes*,*
  • A default font (such as C128A shown below) can be used or you can create your own font

Creating a new font

  • Make sure you are in change mode for System bar codes
  • Click on the New button on the toolbar and then choose the ‘New Bar Code Technology’ and give it a name (eg: BC_NAME) and description

         

 

  • Different barcode technologies are available. For this example we shall use ‘Code 128’
       
  • Keep the other settings as default and save the font in a transport request (clicking on cancel at the transport request popup will save it locally)

2. Creating the Style

  • Go to transaction SMARTSTYLES or use the ‘Style’ radio button of transaction SMARTFORMS
  • Create a new Style (eg: ZBARCODE_STYLE)
  • Create a default Paragraph Format by clicking on ‘Create Node’ from the Context Menu of ‘Paragraph Formats’ in the left navigation view
  • Create a new Character Format by clicking on ‘Create Node’ from the Context Menu of ‘Character Formats’ in the left navigation view (I have given the Character Format the name ‘CH’ in this example)

       

  • From the barcode name dropdown list, choose either a system default value such as C128A or the font that we just created (eg: BC_NAME)

        

  • Save and activate the Style

    3. Creating the Smartform

Here we will create a Smartform to display Material numbers from the MARA table as well as a barcode which contains that same Material number * Go to transaction SMARTFORMS and create a new Smartform (ZBARCODE_MARA in this example)

  • Create the following entry in the Tables tab of the Form Interface of your Smartform

        

  • Save the Smartform
  • Create a new Table entry on the Main Window as shown below

       

  • In the Table tab of %TABLE1,Split the Line type %LTYPE1 into two parts
  • In the Data tab, LOOP AT Internal Table IT_MARA INTO IT_MARA
  • Create a Table Line in the header as well as the Main Area of the table
  • Assign Line type %LTYPE1 to both of them
  • Create a Text value in each of these four cells (you can rename them to make it easier to understand) Your left side navigation display should now look something like this

       

  • Put appropriate headers into the HEADER_MATNR and HEADER_MATNR_CODE text fields
  • Click on the Field list button to display the field list

       

  • Drag and drop the MATNR field from the IT_MARA table interface into the General Attributes tab of the DATA_MATNR as well as the DATA_MATNR_CODE cells

         

  • In the Output Options tab of the DATA_MATNR_CODE cell, choose the style that you created earlier (ZBARCODE_STYLE in this example)
  • Go back to the General Attributes tab, select the text &IT_MARA-MATNR& and then change its character style to ‘CH’
  • Save and activate the Smartform

4. ABAP Program to call the Smartform

  • Go to transaction SE38
  • Create a simple program to call the Smartform
  • Save, activate and run the program
  • You can choose to preview or print the Smartform

Sample program

5. Sample Output

 

·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150
  1. *&———————————————————————*  
  2. *& Report  ZBARCODE_CALLER  
  3. *&  
  4. *&———————————————————————*  
  5. *& call smart form  
  6. *&  
  7. *&———————————————————————*  
  8.   
  9. REPORT  ZBARCODE_CALLER.  
  10.   
  11. TABLES MARA.  
  12.   
  13. DATA FMNAME TYPE RS38L_FNAM.  
  14. DATA ZITAB TYPE STANDARD TABLE OF MARA.  
  15.   
  16. SELECT MATNR FROM MARA INTO CORRESPONDING FIELDS OF TABLE ZITAB  
  17.   UP TO 15 ROWS.  
  18.   
  19. CALL FUNCTION ‘SSF_FUNCTION_MODULE_NAME’  
  20.   EXPORTING  
  21.     FORMNAME = ‘ZBARCODE_MARA’  
  22.   IMPORTING  
  23.     FM_NAME  = FMNAME.  
  24.   
  25. CALL FUNCTION FMNAME  
  26.   TABLES  
  27.     IT_MARA = ZITAB.  

 

 

 

6. Troubleshooting

Consecutive bar-coded rows overlap

Create a new paragraph format and set the Line Spacing to 2 Lines

Use this new format in the non bar-coded cells

Second field shows MATNR in English and not bar-coded

Make sure you select the text before changing the character format in the Smartform

Preview is proper but printing doesn’t take place at all

Make sure your printer is configured properly in transaction SPAD. Try printing something else from SAP to verify the settings

The device type used in our system is SAPWIN   : Rel.4.x/SAPlpd 4.09+_Also check the _Print Immediately button, else go to transaction SP01 to immediately print all pending print jobs

Preview is proper, text is printed properly but barcode (and pictures) are not proper

Check that your printer is configured properly in Windows with the proper driver that matches your printer’s model

Disclaimer and Liability Notice

This document may discuss sample coding or other information that does not include SAP official interfaces and therefore is not supported by SAP. Changes made based on this information are not supported and can be overwritten during an upgrade.

SAP will not be held liable for any damages caused by using or misusing the information, code or methods suggested in this document, and anyone using these methods does so at his/her own risk.

SAP offers no guarantees and assumes no responsibility or liability of any type with respect to the content of this technical article or code sample, including any liability resulting from incompatibility between the content within this document and the materials and services offered by SAP. You agree that you will not hold, or seek to hold, SAP responsible or liable with respect to the content of this document.

How to replicate a material master from R/3 to SRM

This article will present a quick guide for how to replicate material master from a back-end system to SRM 5.0, including all the BASIS(BC) activities. Before we proceed, I would like to point out that these instructions assume that the system has the latest patch level and support packages installed.

  1. Basis setup
  2. First of all, you must set a RFC connectivity between SRM and backend system:

 

 

 

  1.  

    • Create system users for remote connection (RFC_CONN). This user must have SAP_ALL authorization.
    • Create RFC connections in SRM system.
      Go to transaction SM59 on SRM side and click on ‘create’ button. Set RFC destination name to the back-end system <sid>CLNT<client> (eg. R3DCLNT200), connection type to 3 (abap / R/3 connection) and fill the target host to your back-end system. On the Logon/Security tab, add the RFC_CONN user created in the previous step. Save your settings.
    • Create RFC connections in the back-end system.
      Go to transaction SM59 on the back end and click on ‘create’ button. Set the RFC destination name to SRM system <sid>CLNT<client> (eg. SR1CLNT300), connection type to 3 (abap / R/3 connection) and fill in the target host to your back-end system. On the Logon/Security tab, add the RFC_CONN user created in the previous step. Save your settings.
    • Create a logical system name in SRM.
      Go to transaction SPRO -> SAP Implementation Guide -> Supplier Relationship Management -> SRM Server -> Technical Basic Settings -> ALE Settings -> Distribution -> Basic Settings -> Logical Systems -> Define Logical System Add a new value:
      Log.System Name
      R3DCLNT200 R3DCLNT200

      Save your settngs.

    • Create a logical system name in the back end.
      Go to transaction SPRO -> SAP Customizing Implementation Guide -> SAP Web Application Server -> Application Ling Enabling -> Sending and Receiving Systems -> Logical Systems -> Define Logical System Add a new value:
      Log.System Name
      SR1CLNT300 SR1CLNT300

      Save your settngs.

  2. Middleware parameters setup
  3. Go to transaction SM30 on backend system and choose table CRMCONSUM. Add the following values:
    User Active Description Q Prefix
    CRM checked SRM connection R3A

    Next, choose CRMSUBTAB for subscription table for the Up and Download Object and add the following values:

    User ObjectName U/D Obj. Class Function Obj. Type Funct. Name
    CRM empty Download Material empty empty CRS_MATERIAL_EXTRACT
    CRM empty Download Material empty empty CRS_CUSTOMIZING_EXTRACT
    CRM empty Download Material empty empty CRS_SERVICE_EXTRACT

    Next, choose CRMRFCPAR for definitions of RFC Connections and add the following values:

    User ObjectName Destination Load Type INFO InQueue Flag Send XML
    CRM * SR1CLNT300 Initial Download SR1CLNT300 X Send XML
    CRM * SR1CLNT300 Request SR1CLNT300 X Send XML
    CRM MATERIAL SR1CLNT300 Initial Download SR1CLNT300 X Send XML

    Leave all other field empty.
    Now, configure filtering for the material master:
    Choose the CRMPAROLTP table for CRM OLTP Parameters and add the following values:

    Parameter name Param. Name 2 Param. Name 3 User Param. Value Param. Value 2
    CRM_FILTERING_ACTIVE MATERIAL empty CRM X empty

    Now we must edit the table for the application indicator. Go to transaction SE16N on the back-end side and choose table TBE11. Search or add an application component BC-MIDand edit activity settings (field AKTIV = X).
    Save your settings.

  4. Enterprise buyer with/without CRM
  5. In this activity, you define whether you are running the Enterprise Buyer and CRM in the same system. This might accelerate the Master Data download performance. If you are using CRM in the client, then skip this activity.

    In the SRM system run transaction BBP_PRODUCT_SETTINGS, deselect ‘Test mode’ and choose the Execute button.
    The system generates a report containing all tables that have been deactivated.

  6. Generate repository objects
  7. With this procedure, you generate the middleware function modules (BDoc Object Type) for the material master.

    Go to transaction SMOGGEN and choose object PRODUCT_MAT and PRODUCT_SRV. Generate services for all object categories.

  8. Number range object maintenance
  9. On the SRM system, go to transaction SRNO and assign number ranges for all CRM Replication and Realigment Queues. Enter in object field RRSEQ* and hit F4 button. Choose the first object (RRSEQ_AC) and click on the ‘change’ button. On the next screen, click the button ‘Number Ranges’ and add the following range:
    No From number To Number Ext.
    01 00000000000000000001 99999999999999999999 no

    Repeat these steps for all of RRSEQ* objects.

  10. Output format of product ID
  11. Check your output format for Product ID in the backend and SRM system. This values must be the same for both systems.
    Go to transaction COMCPRFORMAT in SRM and check the product ID lenght and values for displaying leading zeroes.

    Save your settings.

  12. CRM middleware monitoring cockpit
  13. On the SRM side, go to transaction SMWP. Open System Settings and double-click on ‘Number of sites per site type’ name. The Administration Console shows up.
    Click on ‘Create Object’ icon and fill in the Name and description. Choose R/3 for ‘Type’ parameter and click on Site Attributes. Enter and create an RFC destination to backend system (R3DCLNT200).

    Save your settings.

  14. Start of download material
  15. Go to transaction R3AS on SRM side and choose the first object to download (DNL_CUST_BASIS3).

    After this, the source site and desitination site on data flow should fill with proper values. Click on the ‘Execute’ button. After this, go to transaction R3AM1 for the monitoring object.

    If you have a green light, go to transaction R3AS again and repeat the steps for objects:

    • DNL_CUST_PROD0
    • DNL_CUST_PROD1
    • DNL_CUST_SRVMAS
    • MATERIAL

    After this, go to transaction COMM_HIERARCHY to see what kind of categories are replicated from backend system.

    Go to transaction COMMPR01 to see what kind of Products are replicated from backend system.

  16. Filtering Material Master records
  17. If you want to filter material master or other objects, go to transaction R3AC1 on SRM side. You will see the following table:

    Click on filter icon on object MATERIAL. On the filter settings tab, choose Source Site Name and click edit button.

    The filter settings object table corresponds to the fields and tables/structures from backend system. If you want to download only one material, enter the following values:

  18. Deleting the material master from SRM
  19. If you want to delete hierarchy of product, run this report:
    • COM_HIERARCHY_DELETE_SINGLE – Deletes all hierarchy and category data
    • COM_HIERARCHY_DELETE_ALL – Deletes all hierarchy and category data

    If you want to delete all or selected product, run this report:

    • COM_PRODUCT_DELETE_SINGLE – Deletion of a product from the database
    • COM_PRODUCT_DELETE_ALL – Deletion of all product master data

     

Using SETLEAF and node Tables

http://biwithjb.wordpress.com/tag/setleaf/

 

BOM (Bill-Of-Materials) Scenario with SQL Server Analysis Services 2008 R2

May 23, 2011

While working on a Business Intelligence project where the data source is SAP R/3 and data is fetched directly from the SAP tables using SSIS 2008 with the Microsoft BizTalk SAP Connector; everything was smooth until one day I had to replicate a multiple parent parent-child relationship (aka bill-of-materials) that SAP creates in the Finance module.

These relationships occur when a child node can have more than one parent. Even though these types of relationships are not supported by default in SQL Server Analysis Services 2008 R2 I kept trying to implement them, until I’ve found a solution that works! In this document I explain how I did it.

In all bill-of-materials scenarios we have one fact table, one dimension table and one bridge or intermediate table that connects the fact table with the dimension table. This document is structured in 4 sections that will cover the 3 tables mentioned before plus one section for the required Analysis Services configuration.

Section 1 – Data Source Scenario

The data source tables are introduced and their structure explained in this section.  The fact table and its relationship with the dimension table are explained here.

This section will be quite familiar to those who have worked in a SAP environment, extracting data directly from SAP tables. If your scenario is not this specific SAP one, you can jump this section completely and go straight to section 2.

Section 2 – Building the Cost Element Dimension

A bill-of-materials dimension table has a slightly different structure than a normal dimension table in a non-BOM case. Section 2.3 covers this difference. The rest of the section details how to flatten the SAP hierarchy of nodes a leaves. If in your case you have a ready-to-go dimension table I still strongly suggest to read section 2.3.

Section 3 – Relate the Fact table with the Dimension table

This section describes the importance of the bridge or intermediate table that will connect the fact table with the dimension table.

Section 4 – Configuring the cube

Finally, section 4 details the configuration needed in Analysis Services 2008 R2 to make all parts work together in a bill-of-materials scenario.

Section 1 – Data source scenario

The specific solution covered in this document retrieves data for the Finance department to report on the expenditure of each department. In SAP terms that means the Cost Centers and Cost Elements, which are stored in the following tables’ structure:

Definition:

  • CSKS: Cost Center definition table
  • CSKA: Cost Element definition table

These two tables contain each individual Cost Center/Element identified by the OBJNR field in CSKS table and the KSTAR field in CSKA table.

Both Cost Centers and Cost Elements are structured in a hierarchy defined in the SETHEADERT, SETNODE and SETLEAF tables:

  • SETHEADERT: Description (long text) for the
    SETNODES
  • SETNODE: Declares for each node (SETNAME) which
    are its children (SUBSETNAME). This table is a normal recursive parent-child
    table. When a node doesn’t have any child it is considered a SETLEAF.
  • SETLEAF: Contains only the last members on the
    SETNODE hierarchy, i.e. the leaf nodes. Each leaf node spans a range of Cost
    Centers/Cost Elements (OBJNR/KSTAR); this range is declared in the VALFROM and
    VALTO fields.

The big problem is that each OBJNR/KSTAR can be in more than one SETLEAF and that one SETNODE can be in more than one SETNODE hierarchy.
This is known as a multi-parent parent-child relationship or bill-of-materials.

To complete the scenario and have an example let’s include our fact table.

COEP table is a fact table that stores for each expense in the company the amount and the cost center and cost element which that expense is related to.

For example, you buy a computer that goes to X (OBJNR) cost center and Y (KSTAR) cost element.

Relationships:

  • COEP is related with CSKS (Cost Center) by OBJNR field.
  • COEP is related with CSKA (Cost Element) by KSTAR field.
  • The rest of the fields are fact data.

Let’s have a simple example:

COEP Fact table:

  • OBJNR: KS100000120000
  • KSTAR: 0000404050

To make things simpler, in this document we will follow the solution for the Cost Element object. To implement the Cost Center object the same steps needs to be
followed.

The KSTAR 0000404050 appears in 2 leaf nodes (SETLEAF), PURSERV and MAINTREN. PURSERV node (SETNODE) appears under at least 2 different
parents as the following screenshot shows:

So, the expense that we have here needs to be reported under 2 different hierarchies but can’t be double billed if there is a parent node for the two hierarchy trees. This is a normal BOM scenario.

Section 2 – Building the Cost Element Dimension

2.1. Retrieve all SETNODE AND SETLEAF records

Using SSIS and the Microsoft BizTalk SAP Connector fetch all nodes and columns from the SAP tables described before and import them to a staging database in SQL Server.

The rows are filtered by the SETCLASS and SUBCLASS columns to retrieve only the ones that I’m interested. In my case SETCLASS = 0101 and SUBCLASS = QA10. These values could, and most probably would, be different in your company.

2.2. Detect the hierarchies and build the staging relationship tables

Hierarchy table for nodes (SETNODE)

Using a recursive stored procedure with a cursor inside, I build  a relationship table that contains all possible paths for the SETNODE hierarchies. The store procedure “walks” the SETNODE table and inserts rows in the Z_SETNODE_HIERARCHY table with the appropriate information.

Notice how the node with SETNAME equal to “109200” appears in 2 different hierarchies; one under “QA10_BJP” and the other under “QA10_CC”. It’s important to understand that this table has ALL possible hierarchy trees, that’s why node “109200” appears multiple times.

Complete LEAF information

A second table will store the complete SETLEAFs information crossed with the definition table CSKA. This table will be called Z_CSKA_SETLEAF.

To build this table I’m using another stored procedure, called p_Z_CSKA_SETLEAF.

Hierarchy table for LEAFs (SETLEAF)

A third table containing the relationships between SETLEAFs and SETNODEs is needed. In this table we have one entry for each KSTAR (SETLEAF) related to a SETNODE. 

If you are interested in the stored procedures that create the tables described above please click in the following link to a Word document which contains the code for the stored procedures in the order that should be executed: BOMStoredProcedures

Note: To execute the stored procedures I use the Execute SQL Taskin SSIS.

The resulting tables from this process are:

2.3. Create the Dimension table DimCostElement

With the previous 2 tables now we can load them into ONE single Dimension table, DimCostElement. First we will load the nodes from the Z_SETNODE_HIERARCHY table and later the leaves from theZ_CSKA_SETLEAF_SETNODE table.

By joining these two tables we have “flattened” the hierarchy. Now as leaves and nodes are in the same table, they can be considered the same object type.

Remember that this Dimension table contains ALL the possible paths in the hierarchy, so a child will appear multiple times in the table. Also remember that a child now can be not only a leaf node member but also normal node member. There is no more distinction between leaf and node members.

The structure of this table is very important, let’s have a deeper look at it:

DimCostElement table contains a CostElementID and a ParentID field. The CostElementID field acts as ID for the table, while the ParentID field just refers to the former to create a parent-child relationship.

The SETNAME field contains the SETNAME for the SETNODEs and the KSTAR for the LEAFs. In this way we have unified the SETNAME for the two different object types. Be aware here that one SETNAME can have multiple CostElementIDs due to that SETNAME will appear in more than one hierarchy path.

The concept of decoupling the name of an object from its database ID, allowing the object name to have more than one ID is what allows us to have multiple-parent parent-child relationships. This is the most important concept to learn in this exercise.

The last two fields are description fields.  

Section 3 – Relate the Fact table with the Dimension table

The fact table, COEP, has for each entry one KSTAR field that relates the described expense with one Cost Element in the dimension table; but because one Cost Element can be in more than one hierarchy the relationship between the fact table and the dimension table cannot be direct. We need an intermediate table.

The intermediate table pairs each KSTAR from the fact table with all possible CostElementIDs in the DimCostElement table. Let’s remember that in our case the KSTAR field from the fact table contains the same value (here is the relationship) as the SETNAME field from the DimCostElement table, but because of the multi-parent parent-child hierarchy nature of the Cost Elements,
one SETNAME can have multiple CostElementIDs. The intermediate table stores for each KSTAR from the fact table all the CostElementIDs from the DimCostElement table (many-to-many relationship).

You can create another stored procedure that fills the fact-less table with the result of a join query between the fact table and the dimension table.

In SQL Server 2008 this type of intermediate tables is known as Fact-less tables. You can search the Books Online for further information about them.

In this solution we cross one of the golden rules in data warehousing. KSTAR values in the Fact table should be replaced by their equivalent CostElementID from the DimCostElement table. But because one KSTAR value can have more than one CostElementID (remember that one KSTAR can be in more than one leaf member), this is a better solution that saves us of creating another intermediate table.

With this last table the database is ready for Analysis Services. In the next section I describe how to configure the cube to allow the BOM relationships and properly aggregate them.

Section 4 – Configuring the cube

4.1. Adding the fact and dimension tables

First step will be to add the 3 previous tables (fact, dimension and intermediate) to the data source view and create the appropriate relationships between tables (if you are as me that I don’t create any relationships in the database itself).

Create an empty cube and let’s add the tables manually to fully control how Analysis Services understand them.

The 3 tables will be added and configured in the cube as follows:

  • The Fact COEP table will be added to the cube a fact table with one measure group.
  • The intermediate Fact-less table will be added to the cube as both a dimension and a fact table.
  • The DimCostElement table will be added to the cube as a dimension table.

The Fact-less table should only be configured as a fact table, as it doesn’t contain any data, but SSAS needs 2 dimensions when configuring many-to-many relationships. The reason behind this is that SSAS approaches many-to-many relationships as 2 regular relationships put together.

You can add first the Fact-less table as a dimension and later as a fact table or on the opposite order, it really doesn’t matter. To not confuse the final user set the “Visible” attribute of the dimension to “False” and do the same for each measure of the fact. This will prevent the false dimension and fact-less table to appear in any client software that queries the cube.

4.2. Configuring the Fact-less dimension

Attributes:

The Fact-less dimension will have 3 attributes:

  • Factless-Cost Element ID: This attribute is the field CostElementID field from the Fact-less table
  • FactCOEPActual – KSTAR: This attribute is the KSTAR field from the Fact COEP table (main fact table)
  • DimCostElement – Cost Element ID: This attribute is the CostElementID field from the DimCostElement table

Note: I like to name the attributes in these special tables with the name of their source table preceding their name for clarity purposes.

These 3 attributes are needed to later build the many-to-many relationships between the tables, as it is explained in the next section of this document.

4.3. Configuring the Dimension Usage

The Dimension Usage tab allows us to define the relationship between the dimensions and the measure groups (fact) contained in a cube. It is here where we can tell Analysis Services that we have a multi-parent parent-child between our 3 tables.

The screenshot below shows the final scenario for the Cost Center (Factless OBJNR Cost Center) and Cost Elements (Factless KSTAR Cost Element). As this document only covers the Cost Element case, let’s focus our attention on the Actual Values and Factless KSTAR Cost Element columns/measure groups and Dim Cost Element (Cost Element) and Factless KSTAR Cost Element rows/dimensions.

In this document I will not describe the different types of dimension relationships. I encourage you to read this topic in the Microsoft MSDN here: http://msdn.microsoft.com/en-us/library/ms175669(v=SQL.105).aspx

First relationship

(Dimension) Factless KSTAR Cost Element with (Measure G)
Factless KSTAR Cost Element:

This is a Regular relationship as the dimension and the measure group are jointly directly by a key column.

Configure the relationship as in the following screenshot:

Second relationship

(Dimension) Dim Cost Element with (Measure G) Factless KSTAR
Cost Element:

This is a Regular relationship as the dimension and the measure group are jointly directly by a key column.

Configure the relationship as in the following screenshot:

Third relationship

(Dimension) Factless KSTAR Cost Element with (Measure G) Actual Values:

This is a Regular relationship as the dimension and the measure group are jointly directly by a key column.

Configure the relationship as in the following screenshot:

Fourth relationship

(Dimension) Dim Cost Element with (Measure G) Actual Values:

This is the most important relationship to define. The Cost Element Dimension is related in a many-to-many fashion to the fact table. Analysis Services needs of an intermediate table (fact) in order to build the relationship.

As our intermediate table has all possible paths for all the hierarchies and the dimension table has a parent-child relationship built-in, this combination will give us the bill-of-materials scenario.

Configure the relationship as in the following screenshot:

After all the configuration is done, build the cube (first build the dimensions and later the measure groups) and start enjoying a multi-parent parent-child relationship (or Bill-of-Materials) in SQL Server Analysis Services 2008.

USri