Human Capital Management Blogs by SAP
Get insider info on HCM solutions for core HR and payroll, time and attendance, talent management, employee experience management, and more in this SAP blog.
cancel
Showing results for 
Search instead for 
Did you mean: 
joncadby
Advisor
Advisor

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