Introduction:
This article covers how to create custom planning solution BW/4 Hana. Its relevant for BPC Embedded or BW-IP (integrated planning).
Basic knowledge of BW IP, HANA and programming is required to understand the concept.
BPC embedded mainly uses BW-IP/PAK for planning. It has many standards functions available (e.g. Copy, Distribution, Repost, etc. ). But, there are many scenarios where the allocation/distribution logic can't be fulfilled using standard planning functions available. In that case we generally go for custom planning solution.
Generally in real-time scenario, there will be multiple levels of allocations also having multiple dimensions. In this article we will be discussing how to create a simple end to end custom planning solution and how to build the logic including error handling. In this scenario, we are going to use the same aDSO for transaction data as well as for allocated data. In real scenario, we can have different aDSO for transaction data and planned data.
Main Content:
Problem Statement:
Source data containing below information.
Costcenter Driver |
|
|
|
|
|
|
|
|
|
|
Scenario |
Calmonth |
Control Area |
Source CC |
Target CC |
Factor |
ACT |
202304 |
E001 |
AEA1001002 |
ABCDULSGR1 |
0.2 |
ACT |
202304 |
E001 |
AEA1001002 |
ABCDULSGR2 |
0.4 |
ACT |
202304 |
E001 |
AEA1001002 |
ABCDULSGR3 |
0.15 |
ACT |
202304 |
E001 |
AEA1001002 |
ABCDULSGR4 |
0.25 |
ACT |
202304 |
E001 |
AEA1001004 |
ABCDULSGR2 |
0.5 |
ACT |
202304 |
E001 |
AEA1001004 |
ABCDULSGR3 |
0.3 |
ACT |
202304 |
E001 |
AEA1001004 |
ABCDULSGR4 |
0.1 |
ACT |
202304 |
E001 |
AEA1001004 |
ABCDULSGR5 |
0.1 |
Transaction Data |
|
|
|
|
|
|
|
|
Scenario |
Calmonth |
Costcenter |
Audit ID |
Amount |
ACT |
202304 |
AEA1001002 |
A_001 |
100 |
ACT |
202304 |
AEA1001004 |
A_001 |
200 |
Audit ID A_001 refers to source data (transaction data).
Audit ID A_002 refers to allocated data.
CC Master |
|
|
|
|
|
|
|
|
|
|
|
|
|
CO_AREA |
COSTCENTER |
OBJVERS |
DATETO |
DATEFROM |
OBJ_CURR |
PROFIT_CTR |
E001 |
ABCDULSGR1 |
A |
31-12-9999 |
01-01-2017 |
USD |
ABCDULSGR1 |
E001 |
ABCDULSGR2 |
A |
31-12-9999 |
01-01-2017 |
USD |
|
E001 |
ABCDULSGR3 |
A |
31-12-9999 |
01-01-2017 |
USD |
ABCDULSGR3 |
E001 |
ABCDULSGR4 |
A |
31-12-9999 |
01-01-2017 |
USD |
ABCDULSGR4 |
E001 |
ABCDULSGR5 |
A |
31-12-9999 |
01-01-2017 |
USD |
ABCDULSGR5 |
The requirement is to allocate the amount to receiver costcenters based on driver value. It will generate the allocated values with different audit ID. It should consider the profit% value entered by user as well.
Error handling: System should also validate if any of the receiver costcenter is not assigned to profit center. It should throw error message.
Parameter handling: User will pass the profit percentage value (%) as parameter. It will be used in the allocation to multiply all allocated amounts.
Solution:
Driver Data:
Create a planning enable aDSO for costcenter driver. Enable hana view generation.
![](/legacyfs/online/storage/blog_attachments/2023/08/CC-Driver-1.jpg)
Create the aggregation level and input sheet. Update the CC driver values as mentioned before.
Update the CC master data as mentioned before.
Transaction Data:
The below objects will be needed.
- Create the aDSO for transaction data.
- Create aggregation level.
- Custom class
- Planning function type using custom class
- Planning function.
- Planning sequence and filter.
aDSO for transaction data:
![](/legacyfs/online/storage/blog_attachments/2023/08/adso.jpg)
Aggregation level:
Create the aggregation level on top of transaction data aDSO. Pick all the fields from aDSO.
![](/legacyfs/online/storage/blog_attachments/2023/08/aggr-level.jpg)
Custom Class
Create a Z empty class with interface IF_RSPLFA_SRVTYPE_IMP_EXEC.
![](/legacyfs/online/storage/blog_attachments/2023/08/empty-class-2.jpg)
Planning Function Type:
Create new planning function type using the class created above. Add a parameter as profit % (numeric value). info object 0SPL_TARGET is used.
![](/legacyfs/online/storage/blog_attachments/2023/08/Planning-func-type-1.jpg)
Planning Function:
Create a planning function based on the planning function type. use the input ready formula variable for the profit %.
![](/legacyfs/online/storage/blog_attachments/2023/08/planning-func.jpg)
Planning function Filter:
![](/legacyfs/online/storage/blog_attachments/2023/08/Planning-filter.jpg)
Planning Sequence:
![](/legacyfs/online/storage/blog_attachments/2023/08/Planning-Seq.jpg)
Logic Build:
Run the program RSPLS_SQL_SCRIPT_TOOL. Provide the parameters like below and execute. System will give code dump. Copy the code to the class created before.
![](/legacyfs/online/storage/blog_attachments/2023/08/code-template-1.jpg)
Adjust the code as mentioned below. System generates the template code. The logic needs to be built in MY_HANA_PROCEDURE method. Its take into consideration of all the parameters defined for the planning function type.
CLASS zcl_cc_to_cc_demo DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
* Type for aggregation level ZAL_SRCAL
TYPES: BEGIN OF y_s_zal_srcal,
calmonth TYPE /bi0/oicalmonth,
costcenter TYPE /bi0/oicostcenter,
co_area TYPE /bi0/oico_area,
zc_audit TYPE /bic/oizc_audit,
zc_scnro TYPE /bic/oizc_scnro,
currency TYPE /bi0/oicurrency,
amount TYPE /bi0/oiamount,
END OF y_s_zal_srcal.
TYPES: y_t_zal_srcal TYPE STANDARD TABLE OF y_s_zal_srcal.
INTERFACES if_amdp_marker_hdb.
INTERFACES if_rsplfa_srvtype_trex_exec.
INTERFACES if_rsplfa_srvtype_imp_exec.
CLASS-METHODS: my_hana_procedure IMPORTING VALUE(i_view) TYPE y_t_zal_srcal
VALUE(i_profit) TYPE /bi0/oispl_target
EXPORTING VALUE(e_view) TYPE y_t_zal_srcal
VALUE(e_msg) TYPE if_rspls_sql_script=>y_t_msg
RAISING cx_amdp_error.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS zcl_cc_to_cc_demo IMPLEMENTATION.
METHOD if_rsplfa_srvtype_trex_exec~trex_execute.
DATA: l_r_sql_script TYPE REF TO if_rspls_sql_script,
l_procedure_name TYPE string,
l_t_iobj_param TYPE if_rsr_pe_adapter=>tn_t_iobj_param.
l_r_sql_script = cl_rspls_session_store_manager=>get_sql_script_instance( i_r_store = i_r_store ).
* The method if_rspls_sql_script~get_parameter_values returns a table of parameters
* with the values given in the function definition
l_r_sql_script->get_parameter_values(
EXPORTING
i_r_param_set = i_r_param_set
i_para_name_for_procedure = 'HANA_PROCEDURE_NAME'
IMPORTING
e_procedure_name = l_procedure_name
e_t_iobj_param = l_t_iobj_param ).
* The function parameter given mehtod paramenter i_para_name_for_procedure
* will not be returned in the table e_t_iobj_param but the value of this
* function parameter will be returned int the mehtod parameter e_procedure_name
* This mechanisme can e.g. be used to define function specific SAP-HANA-procedure names.
* Other examples to get the name of the SAP-HANA-procedure name which will be called can be
* - you use the technical name of the planning function type:
* - or you simply give the SAP-HANA procedure name in a string:
l_procedure_name = 'ZCL_CC_TO_CC_DEMO=>MY_HANA_PROCEDURE'.
r_s_view-view = l_r_sql_script->execute_sql_script(
i_view = i_view
i_t_iobj_param = l_t_iobj_param
i_proc_name = l_procedure_name
i_sql_script_returns_ai = abap_true
i_r_msg = i_r_msg ).
ENDMETHOD.
METHOD if_rsplfa_srvtype_trex_exec~init_and_check.
e_trex_supported = rs_c_true.
ENDMETHOD.
METHOD my_hana_procedure BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT
* For Options and declarations check the AMDP documentiation (ABAP Managed Database Procedure)
OPTIONS READ-ONLY
* OPTIONS SUPPRESS SYNTAX ERRORS READ-ONLY. "
USING /BI0/QCOSTCENTER.
DECLARE lv_count INTEGER;
* ERROR HANDLING *
lt_invalid = SELECT a."0RESP_CCTR" as cctr
FROM "_SYS_BIC"."system-local.bw.bw2hana/ZAD_CCDRV" as a
JOIN "/BI0/QCOSTCENTER" as b
ON a."0RESP_CCTR" = b.costcenter
AND a."0CO_AREA" = b.co_area
WHERE b.profit_ctr = '';
SELECT COUNT (*) INTO lv_count FROM :lt_invalid;
IF :lv_count <> 0 THEN
e_msg = SELECT * from :e_msg UNION
SELECT '00' as msgid,
'E' as msgty,
'001' as msgno,
'Profit Center is missing' as msgv1,
'' as msgv2,
'' as msgv3,
'' as msgv4
FROM dummy;
RETURN;
END IF;
* ALLOCATION *
e_view = SELECT * FROM :i_view where zc_audit = 'A_001'
UNION ALL
SELECT a.calmonth,
b."0RESP_CCTR" as costcenter,
b."0CO_AREA" as co_area,
'A_002' as zc_audit,
a.zc_scnro,
a.currency,
(( a.amount * b."0RSPL_FCWHT" )* (1+:i_profit)) as amount
FROM :i_view as a
JOIN "_SYS_BIC"."system-local.bw.bw2hana/ZAD_CCDRV" as b
ON a.calmonth = b."0CALMONTH"
AND a.co_area = b."0CO_AREA"
AND a.costcenter = b."0REQU_CCTR";
ENDMETHOD.
ENDCLASS.
Execution:
First convert the aDSO to load mode. Then load the transaction data as mentioned before in manage datastore (BW cockpit). Convert the aDSO to plan mode.
Run1: (Verify the profit center)
![](/legacyfs/online/storage/blog_attachments/2023/08/Run1.jpg)
System will throw error as ABCDULSGR2 costcenter is having blank profit center.
![](/legacyfs/online/storage/blog_attachments/2023/08/Run1-validation.jpg)
Run2: Allocation test
Update the profit center field of ABCDULSGR2 with any value. Then run the planning sequence with same selection. After execution save the plan buffer.
![](/legacyfs/online/storage/blog_attachments/2023/08/Run2.jpg)
verify the values in LISTCUBE Tcode for the aDSO. The source values got allocated to receiver costcenters with profit of 8%.
![](/legacyfs/online/storage/blog_attachments/2023/08/output-1.jpg)
Conclusion:
In this article we learnt the below things.
- How to create a Custom planning function using AMDP.
- How to handle error validation in planning function.
- How to pass parameter to a planning function and how to use in the logic.
- How to write the logic in AMDP planning method.
- How to create end to end solution for custom solution in planning with all BW planning objects and AMDP class.