Custom Planning Solution in BW/4 HANA

[jyoti_s_sahu]

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.

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.

1. Create the aDSO for transaction data.


2. Create aggregation level.


3. Custom class


4. Planning function type using custom class


5. Planning function.


6. Planning sequence and filter.

aDSO for transaction data:

Aggregation level:

Create the aggregation level on top of transaction data aDSO. Pick all the fields from aDSO.

Custom Class

Create a Z empty class with interface IF_RSPLFA_SRVTYPE_IMP_EXEC.

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.

Planning Function:

Create a planning function based on the planning function type. use the input ready formula variable for the profit %.

Planning function Filter:

Planning Sequence:

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.

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)

System will throw error as ABCDULSGR2 costcenter is having blank profit center.

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.

verify the values in LISTCUBE Tcode for the aDSO. The source values got allocated to receiver costcenters with profit of 8%.

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.