Skip to Content
Author's profile photo Naramani Siddu

Step-by-Step procedure for creation, execution and storing of ABAP Managed Database Procedures in HANA

ABAP Managed Database Procedure
Database Procedures are stored and executed in the Database. We can create and execute database procedures in HANA database through ABAP using AMDP Class and AMDP Method called ABAP Managed Database Procedures. SQL SCRIPT is the language for creating stored procedures in HANA. Main benefit of using SQL Script is to allow the execution of complex calculations inside HANA database. The language is varies from one database system to another.The ABAP Managed Database procedures should be created using ABAP Development Tools (Eclipse or HANA Studio).

Creation of ABAP Managed Database Procedure in ABAP
1. Open ABAP Development Tool ( Eclipse or HANA studio ) and Go to ABAP Perspective.           Create new ABAP Class.

2. Provide Name and Description. Click on NEXT Button.



3. Click on Finish button.

4. AMDP Class Definition
An AMDP is implemented in an AMDP class with a regular static method or instance method in any visibility section. The editing environment for AMDP is the ABAP class editor.
The AMDP class must contain the appropriate tag interface. IF_AMDP_MARKER_HDB is Marker Interface for DB Procedures.
Example:
a. In Class Definition provide interface IF_AMDP_MARKER_HDB.
b. Define the table type TT_ORDER and structure type TY_ORDER.
c. Define the method GET_SALESORDER_DETAILS (Method parameters should be Passed       by value).

Logic:

CLASS zcl_salesorder_details DEFINITION
 PUBLIC
 FINAL
 CREATE PUBLIC.
PUBLIC SECTION.
*Marker interface for Database Procedures
 INTERFACES: if_amdp_marker_hdb.
*Structure
 TYPES:
 BEGIN OF ty_order,
   vbeln      TYPE vbeln,    “Sales Order Number
   posnr      TYPE posnr_va, “Item Number
   vkorg      TYPE vkorg,    “Sales Organization
   item_price TYPE netwr_ap, “Item Price
   status         TYPE char30,   “Delivery Status
 END OF ty_order.
* Table type
 TYPES:
  tt_order TYPE STANDARD TABLE OF ty_order WITH EMPTY KEY.
* Method Definition
 CLASS-METHODS get_salesorder_details
    IMPORTING
      VALUE(iv_vbeln) TYPE vbeln
    EXPORTING
      VALUE(et_order) TYPE tt_order.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.

5. Implementaion of AMDP Method in AMDP Class

Logic:

CLASS zcl_salesorder_details IMPLEMENTATION.
METHOD get_salesorder_details BY DATABASE PROCEDURE
                             FOR HDB
                             LANGUAGE SQLSCRIPT
                             OPTIONS READONLY
                             USING vbak vbap vbup.
*To get Sales Order details
et_order = SELECT vbak.vbeln,
                  vbap.posnr,
                  vbak.vkorg,
                  vbap.netwr as item_price,
                  CASE LFSTA
                       WHEN ‘ ‘ then ‘Not Relevant’
                       WHEN ‘A’ then ‘Not yet processed’
                       WHEN ‘B’ then ‘Partially processed’
                       WHEN ‘C’ then ‘Completely processed’
                  END AS status
            FROM vbak AS vbak INNER JOIN vbap AS vbap
                  ON vbak.vbeln = vbap.vbeln
            INNER JOIN vbup AS vbup
                  ON vbup.vbeln = vbap.vbeln AND vbup.posnr = vbap.posnr
            WHERE vbak.vbeln = iv_vbeln;ENDMETHOD.
ENDCLASS.

6. Activate the AMDP Class and Check the created class in Transaction SE24.

 

Execute the ABAP Managed Database Procedure through Report
1. Create a New ABAP Program.


2. Provide Name and Description. Click on NEXT button.

3. Click on Finish button.

4. Call the AMDP Method in ABAP editor.

Logic:

REPORT zr_call_amdp.
PARAMETER p_vbeln TYPE vbeln.
* To Call AMDP Method
zcl_salesorder_details=>get_salesorder_details(
                EXPORTING iv_vbeln = p_vbeln
                IMPORTING et_order = data(lt_order) ).
* To display Sales Order Details
cl_demo_output=>display_data( name = ‘Sales Order Details’
                              value = lt_order ).

Output:
Provide the sales order number as the input

Stored in HANA Database
1. Check the DB Connection in tcode DBACOCKPIT.

2. Database procedure will create in HANA DB at the first call of AMDP Method .
3. Go to SAP HANA Development perspective –> HANA DB System –> Catalog –>
    Schema –> Procedures.
    The AMDP Method Implementation will be stored as Database procedure
    and Table Types of AMDP Class also stored under Schema ‘SAPABAP1’.

4. The Table TypeTT_ORDER ‘ of AMDP Class will be stored as                              “ZCL_SALESORDER_DETAILS=>GET_SALESORDER_DETAILS=>P00000#ttyp”

5.The AMDP Method ‘GET_SALESORDER_DETAILS‘ of AMDP Class
   ‘ZCL_SALESORDER_DETAILS‘ will be stored as Database procedure
   ‘ZCL_SALESORDER_DETAILS=>GET_SALESORDER_DETAILS’ as shown below.

Logic:
create procedure
  “ZCL_SALESORDER_DETAILS=>GET_SALESORDER_DETAILS”
(
  in “IV_VBELN” NVARCHAR (000010),
  out “ET_ORDER” “ZCL_SALESORDER_DETAILS=>GET_SALESORDER_DETAILS=>P00000#ttyp”
)
language sqlscript sql security invoker reads sql data as begin
–To get Sales Order details
  et_order = SELECT vbak.vbeln,
                    vbap.posnr,
                    vbak.vkorg,
                    vbap.netwr as item_price,
                    CASE LFSTA
                        WHEN ‘ ‘ then ‘Not Relevant’
                        WHEN ‘A’ then ‘Not yet processed’
                        WHEN ‘B’ then ‘Partially processed’
                        WHEN ‘C’ then ‘Completely processed’
                    END AS status
             FROM “ZCL_SALESORDER_DETAILS=>VBAK#covw” AS vbak
             INNER JOIN “ZCL_SALESORDER_DETAILS=>VBAP#covw” AS vbap
                        ON vbak.vbeln = vbap.vbeln
             INNER JOIN “ZCL_SALESORDER_DETAILS=>VBUP#covw” AS vbup
                        ON vbup.vbeln = vbap.vbeln AND vbup.posnr = vbap.posnr
                        WHERE vbak.vbeln = iv_vbeln;
end;

6. ZCL_SALESORDER_DETAILS=>GET_SALESORDER_DETAILS#stb2#20160831121018              and
    ZCL_SALESORDER_DETAILS=>GET_SALESORDER_DETAILS#stub#20160831121018            are for calling Database procedure
“ZCL_SALESORDER_DETAILS=>GET_SALESORDER_DETAILS”

Logic:
create procedure
  “ZCL_SALESORDER_DETAILS=>GET_SALESORDER_DETAILS#stb2#20160831121018”
(
  in “IV_VBELN” NVARCHAR (000010)
)
language sqlscript sql security invoker reads sql data as begin
   call “ZCL_SALESORDER_DETAILS=>GET_SALESORDER_DETAILS” (
     “IV_VBELN” => :IV_VBELN ,
     “ET_ORDER” => :ET_ORDER
   );
   select * from :ET_ORDER;
end;

 

Logic:
create procedure
   “ZCL_SALESORDER_DETAILS=>GET_SALESORDER_DETAILS#stub#20160831121018”
(
   in “IV_VBELN” NVARCHAR (000010)
)
language sqlscript sql security invoker reads sql data as begin
  call “ZCL_SALESORDER_DETAILS=>GET_SALESORDER_DETAILS” (
   “IV_VBELN” => :IV_VBELN ,
   “ET_ORDER” => :ET_ORDER
  );
  select * from :ET_ORDER;
end;

 

7. The database tables VBAK VBAP and VBUP are used in AMDP Method will be created as          VIEWS in HANA Database system.
i) ZCL_SALESORDER_DETAILS=>VBAK#covw

ii) ZCL_SALESORDER_DETAILS=>VBAP#covw

iii) ZCL_SALESORDER_DETAILS=>VBUP#covw


Thank you,
Mani

 

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Martin Schlegel
      Martin Schlegel

      Thank you very much for sharing!

      Martin

       

      Author's profile photo Mostapha LAHIT DADI
      Mostapha LAHIT DADI

      Hello Martin,

       

      Very good explanation. Thanks for sharing your knowledges.

       

      Regards,

      Author's profile photo kyo choi
      kyo choi

      Excellent blog.  Keep up the good work.  Thanks.

      Author's profile photo Reynaldo Tupino
      Reynaldo Tupino

      Very good   blog. Thanks

      Author's profile photo nikhil k
      nikhil k

      Very nice explanation. Thank you.

      Author's profile photo Mohit Agrawal
      Mohit Agrawal

      Hi Mani,

      Nice presentation.

      Can you please let me know how can I achieve below through AMDP.

      1. MAT_SALES table has commision group and materials. Many materials for a commision group.
      2. If I want to explode my incoming data that every record for a commision group is exploded into as many records as number of materials in MAT_SALES table for that particular commision gorup. Every data column would be same except the material for these exploded records?

      Any idea?

       

      Thanks

      Mohit

      Author's profile photo Purav Barot
      Purav Barot

      If lfsta field is throwing an error, Update the lfsta field to vbup.lfsta 

      It's a standard field within the SAP vbup table.

      Author's profile photo Mostapha LAHIT DADI
      Mostapha LAHIT DADI

      Hi Martin,

       

      Nice explanation and thanks for sharing your knowledges.

       

      Mostapha LAHIT.

      Author's profile photo Pavel Bashurov
      Pavel Bashurov

      Very good explanation. Thanks

      Author's profile photo Marina Korotkova
      Marina Korotkova

      Hello!

      It was taken from the video by Anubhav Oberoy - Debugging AMDP from ABAP to HANA system.