Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
Showing results for 
Search instead for 
Did you mean: 
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.

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.


  • 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.


-Table used to maintain the threshold values.

- Structure used in the example.

- AMDP Class
AMDP class:


INTERFACES if_amdp_marker_hdb .



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;


-ABAP program
Calling an AMDP class inside an ABAP program

*& Report zpn_amdp1
REPORT zpn_amdp1.

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

"Display o/p
value = lt_op



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.
Labels in this area