Display Custom Data in a SPM Plan Communicator Document

[joncadby]

Overview

The purpose of this article is to show how to include any data that is available in the SAP Commissions database (TCMP and EXT schemas) in a SPM Plan Communicator Document.

Introduction

Plan Communicator Documents can display the following Commissions elements:

• Formula


• Rate Table


• Fixed Value


• Territory


• Variable


• Quota


• Data Fields (Position, Participant, Title, Position Group)

The elements above are directly associated to the user via their plan assignment. It is possible to display anything stored in the SAP Commissions database (TCMP and EXT schemas) in a SPM Plan Communicator Document using a Formula with the “Query for String” rule function.

Some common use cases for this solution are:

• MDLT


• Category and Classifier


• Territory & Quota objects


• Custom database tables

Worked Example

The solution is described in this section by way of a worked example in a HANA database. Similar steps would apply to an Oracle environment.

Step 1: Create a HANA Function

While not required, it is recommended that the logic to retrieve the data is compiled in a HANA function. The following example retrieves an MDLT and formats it as an HTML table

create or replace function EXT.JC_FNC_LT_Bonus_Lookup(i_positionSeq bigint default null, i_periodSeq bigint default  null) returns v_ret varchar(32000) as

begin

  declare v_eot date := '2200-01-01';

  declare v_mdlt varchar(255) := 'LT_Bonus_Lookup';

  declare cursor c_mdlt for

  select mdlt.name as mdlt_name, re.description as mdlt_desc,

  dim0.name as dim0_name, ind0.minstring as dim0_value,

  dim1.name as dim1_name, to_char(cast(ind1.minvalue as integer)) as dim1_value,

  to_char(cast(cell.value as decimal(25,2))) as cell_value,

  row_number() over (order by ind0.displayorder, ind1.displayorder) as rn

  from cs_relationalmdlt mdlt

  join cs_ruleelement re on mdlt.ruleelementseq = re.ruleelementseq and re.removedate = :v_eot and re.effectivestartdate = mdlt.effectivestartdate

  join cs_mdltdimension dim0 on mdlt.ruleelementseq = dim0.ruleelementseq and dim0.removedate = :v_eot and dim0.dimensionslot = 0

  join cs_mdltindex ind0 on mdlt.ruleelementseq = ind0.ruleelementseq and ind0.removedate = :v_eot and ind0.dimensionseq = dim0.dimensionseq

  join cs_mdltdimension dim1 on mdlt.ruleelementseq = dim1.ruleelementseq and dim1.removedate = :v_eot and dim1.dimensionslot = 1

  join cs_mdltindex ind1 on mdlt.ruleelementseq = ind1.ruleelementseq and ind1.removedate = :v_eot and ind1.dimensionseq = dim1.dimensionseq

  left outer join cs_mdltcell cell on cell.mdltseq = mdlt.ruleelementseq and cell.removedate = :v_eot and cell.dim0index = ind0.ordinal and cell.dim1index = ind1.ordinal

  where mdlt.removedate = :v_eot 

  and mdlt.name = :v_mdlt

  order by ind0.displayorder, ind1.displayorder;  

  for x as c_mdlt

  do

    if :x.rn = 1 then      

      v_ret := '<p><b>'||:x.mdlt_name||' '||:x.mdlt_desc||'</b></p>'

            || '<table class="ruleElementTable table table-condensed">'

            || '<thead><tr><th>'||:x.dim0_name||'</th><th>'||:x.dim1_name||'</th><th>Value</th></tr></thead>';

    end if;

    v_ret := :v_ret || '<tr><td>'||:x.dim0_value||'</td><td>'||:x.dim1_value||'</td><td>'||:x.cell_value||'</td></tr>';  

  end for;

  v_ret := ifnull(:v_ret || '</table>', 'MDLT "'||:v_mdlt||'" not found.');

end;

 

Step 2: Add Query to CS_PluginQuery

This step allows the query to be used by the Commission rules.

insert into CS_PluginQuery (tenantId, name, query) values (<TENANT_ID>, 'LT_Bonus_Lookup', 'select EXT.JC_FNC_LT_Bonus_Lookup(positionSeq, periodSeq) from (select $positionSeq as positionSeq, $periodSeq as periodSeq from dummy)');

commit;

 

Step 3: Create Formula

A formula is created that calls the database function.

Formula

 

Step 4: Add Dummy Rule to Plan

Create a deposit rule that will never fire (Conditions: false) that uses the formula from the previous step (Generic Attribute 1: F_Plan_Communicator_LT_Bonus_Lookup)

Deposit Rule

Add the rule to the plan.

Step 5: Add Formula to Plan Communicator Document

You can now use the formula in the Plan Communicator Documents to display the required data.

Document

Result:

Result

 

Technical Notes

• The max length of a VARCHAR/NVARCHAR returned by a HANA is documented as 8388607.


• The “Query for String” will timeout after 5 seconds.


• The return string can include HTML tags for formatting.


• The period input parameters are set to the leaf level period that is effective for the end date of the distribution.


• The performance of the function should be considered to avoid timeouts. If the query is complex, then the data can be prebuilt in a custom table.


• A deposit rule is used because:
  
  
  
  • It has considerably fewer evaluations compared to a credit rule
  
  
  • It does not create unwanted objects like a measurement or incentive rule