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.
Plan Communicator Documents can display the following Commissions elements:
- Rate Table
- Fixed Value
- 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:
- Category and Classifier
- Territory & Quota objects
- Custom database tables
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.
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)
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.
- 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