Implementing Open Item Analysis(OIA) in SAP HANA using AMDP
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: 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.
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 ).
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.