Implementing Open Item Analysis(OIA) in SAP HANA using AMDP

[Devogic]

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.