Welcome, ABAP developers, to the next chapter in this ongoing series focused on learning SAP HANA!

This article aims to provide SAP architects and developers with guidance on factors to consider when implementing OIA using the native capabilities of SAP HANA.

Managing accounts receivable is a critical process for any business. One key analysis is identifying overdue customer invoices, also known as open item analysis (OIA). Performing OIA efficiently can help collections teams collect payments faster. This article explores using SAP HANA and ABAP Managed Database Procedures (AMDPs) to optimize OIA in SAP environments. 

In my recent blog post, I have provided an in-depth overview of the built-in SAP HANA SQL functions that are useful when implementing an Open Item Analysis scenario. I encourage you to check out the post.

https://blogs.sap.com/2023/10/30/most-common-built-in-sql-functions-that-are-provided-with-sap-hana./

Open item analysis (OIA) calculates the number of unpaid or overdue invoices for customers in an organization. This helps accounts receivable teams identify and follow up with customers exceeding payment terms.

In SAP, OIA can be implemented using SQLScript and ABAP Managed Database Procedures (AMDPs). AMDPs allow developers to leverage the performance of HANA by executing data-intensive logic inside the database; this technique is known as Code push down.

Code-to-data or code push down technique→ This involves performing data-intensive operations like calculations, aggregations, and logic inside the SAP HANA database rather than in the ABAP application layer.

By pushing processing into HANA, only the final result set needs to be returned to the ABAP system. This minimizes data transfer and leverage’s HANA’s optimized in-memory processing power.

An AMDP defines a static function containing SQLScript code in an ABAP class. When called from ABAP, it creates a corresponding procedure in the HANA database.

Key points about AMDPs:

• AMDPs are SQL script containers and supported by a HANA db only.
• The ABAP class must implement the IF_AMDP_MARKER_HDB interface.
• AMDPs only allow importing and exporting parameters, not return values.
• Parameters must be passed by value.
• Tables must be specified using the USING clause.
• The HANA procedure is generated only upon first invocation from ABAP.

Note: A procedure in HANA will not be created on activation, in fact, it will be created when we call an AMDP for the first time in the ABAP system. So, multiple versions will be created upon calling an AMDP.

Benefits of AMDPs:

• Enables top-down transport of SQLScript via HTA containers in ABAP.
  As the name suggests, AMDPs are created and managed at the ABAP layer, so supports the Top-Down approach. Hence, it can be transported via HTA container(HANA transport for ABAP) easily.
• Tight integration between ABAP and HANA development.

Note: Top-down approach- Artifacts which are created and managed at ABAP level and on activation or calling a HANA view/procedure gets created in the HANA system.

Drawbacks:

• Requires handling client dependencies in SQLScript code.
• No support for data locking or LUW of updates.
• Debugging SQLScript can be challenging. We can use the CX_AMDP_ERROR class to trace generic runtime issues.

Example:

-Table used to maintain the threshold values.

– Structure used in the example.

– AMDP Class

AMDP class:

CLASS zpn_amdp1 DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb .
    CLASS-METHODS get_oia EXPORTING VALUE(et_oia) TYPE ztt_oia.

  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.


CLASS zpn_amdp1 IMPLEMENTATION.

  METHOD get_oia BY DATABASE PROCEDURE
                 FOR HDB
                 LANGUAGE SQLSCRIPT
                 OPTIONS READ-ONLY
                 USING zdp_cust snwd_bpa snwd_so_inv_head snwd_so_inv_item.

    ---------Data declaration
         declare lv_today date;
         declare lv_client nvarchar(3);
         declare lv_max_amt decimal(15,2);
         declare lv_max_opn_days integer;
         declare lv_tar_curr nvarchar(4);

   -----------1.Get current date, which will be used to calculate open days till today
         select current_date into lv_today from dummy;

    ----------2.Get threshold data from a custom table(max limit)
        ---2.1 Fetch data from "zdp_cust" table for specific user
         select mandt, max_gross_amount , max_open_days , currency_code
           into lv_client, lv_max_amt , lv_max_opn_days , lv_tar_curr
           from zdp_cust
           where usrid = ( select ucase(session_context('APPLICATIONUSER')) FROM dummy );

    ----------3.Open days calculation
        ---3-1. Fetch (number of days)gap between today's date and the date on which invoice was changed for
        ------- blank payment status
        lt_hd_days = select buyer_guid,
                            days_between( to_timestamp(left( changed_at,14),'YYYYMMDDHHMISS') ,
                                          to_timestamp(localtoutc(now( ),'CET'))) as open_days
                     from snwd_so_inv_head
                     where client = :lv_client and
                           payment_status = ' ';

        ---3-2.Join Invoice header with BP to get the combined data based on Company and BP id to get AVG days
        lt_hd_bp = select company_name, bp_id, avg( open_days ) as Open_days_avg
                          from :lt_hd_days as hd
                          INNER join snwd_bpa as bp
                          on hd.buyer_guid = bp.node_key
                          where client = :lv_client
                          GROUP by bp.bp_id, bp.company_name;

    ---------4.Gross Amount calculation
       ---4.1. Get invoice item data for respective BP and currency
        lt_hd_itm_bp_amt = select bp.bp_id, itm.currency_code, sum(itm.gross_amount) as Gross_Amount
                                 from snwd_so_inv_head as hd
                                 inner join
                                 snwd_so_inv_item as itm
                                 on hd.node_key = itm.parent_key
                                 inner join
                                 snwd_bpa as bp
                                 on hd.buyer_guid = bp.node_key
                                 where itm.client = bp.client and
                                       payment_status = ' '
                                 group by bp.bp_id, itm.currency_code;

        ---4.2. Convert Gross amount in desired currency
         lt_conv_amt = CE_CONVERSION(:lt_hd_itm_bp_amt, [
                                                            family             = 'currency',
                                                            method             = 'ERP',
                                                            steps              = 'shift, convert, shift_back',
                                                            target_unit        = :lv_tar_curr,
                                                            client             = :lv_client,
                                                            source_unit_column = 'CURRENCY_CODE',
                                                            reference_date     = :lv_today,
                                                            output_unit_column = 'CONV_CURR_CODE'
                                                        ],  [gross_amount]);

        ---4.3. Add the converted gross amount for each BP
             lt_gross_amt = select bp_id, sum( gross_amount ) as gross_amount, conv_curr_code as currency_code
                                   from :lt_conv_amt
                                   group by bp_id, conv_curr_code;

    ----------5.Prepare final o/p table i.e et_oia
         ----5.1. Setting the flag against the BP if avg open days and gross amount are exceeding the threeshold
         et_oia = select client, bp.bp_id, bp.company_name,
                         t_opn_dys.Open_days_avg as Open_Days,
                         t_opn_amt.gross_amount as Gross_Amount,
                         t_opn_amt.currency_code as Currency_code,
               case when t_opn_dys.open_days_avg > :lv_max_opn_days and
                         t_opn_amt.gross_amount  > :lv_max_amt
                    then 'X'
                    else ''
               end as tagging
                    from snwd_bpa as bp
                    inner join :lt_hd_bp as t_opn_dys
                      on bp.bp_id = t_opn_dys.bp_id
                    inner join :lt_gross_amt as t_opn_amt
                      on bp.bp_id = t_opn_amt.bp_id;


  ENDMETHOD.
ENDCLASS.

-ABAP program

Calling an AMDP class inside an ABAP program

*&---------------------------------------------------------------------*
*& Report zpn_amdp1
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zpn_amdp1.

"Call AMDP class and execute method - get_oia
zpn_amdp1=>get_oia(
  IMPORTING
    et_oia = DATA(lt_op) ).

"Display o/p
cl_demo_output=>display_data(
  EXPORTING
    value = lt_op
).

 Result:

In summary, AMDPs allow developing OIA logic optimized for HANA in ABAP itself. By pushing OIA logic into the HANA database using AMDPs, we can speed up computation and leverage HANA’s in-memory performance. 

Weighing technical tradeoffs is key to choose the best implementation approach.

Please feel free to provide any feedback to improve the content quality further. I’d be happy to clarify or refine it as needed.