IPD v11.0.1.43, single tenant, cloud, SQL Server. Business has about a dozen or so bonus programs. Each program has different criteria, run dates/frequencies, and payout amounts. We're attempting to use a single flow to create the EmployeeSpecialIncentivePayout (ESIP) business class records for the desired program. We figure that all the ESIP entries can be done in one Landmark transaction node, and all that we need to vary is the SQL that runs to get the people and payouts and other ESIP data. Some of the SQL is pretty complex, so SQL Procedures would be our preferred method. We set up multiple triggers in rich client, each one on it's own schedule, all calling the same flow. They all contain a variable called 'BonusType' that hold which sql proc to call. So the PfiTriggerVariable table looks like this: 1 BonusType NewHire 2 BonusType Longevity 3 BonusType GiveDave500Bucks We then created multiple SQL Procs that have identical output formats: lm_ghr.spGetBonusNewHire lm_ghr.spGetBonusLongevity lm_ghr.spGetBonusGiveDave500Bucks you get the picture... Within the flow, we created a vSQL variable, grabed that 'BonusType' value and then in an assign node, make vSQL= "CALL PROC lm_ghr.spGetBonus" + BonusType. Our hope was to then put that variable into the SQL node's 'Run SQL Procedure' section and voila! Dynamically call the correct proc for the triggered bonus. We could then leave the bonus processing flow alone and add/remove bonuses through the rich client triggers, creating the corresponding SQL procs as needed. Seemed good on paper.... In the SQL node, the 'Run SQL Query' allows the use of variables, but the 'Run SQL Procedure' does not seem to. I'd rather not create a separate SQL node for each program. That would seem to defeat the purpose of this whole attempt.
Can this idea even be accomplished? Is there a setting I'm overlooking? Another way to accomplish the same goal? Any thoughts or workarounds are welcome.
Well, we opened up a xtreme ticket and are waiting for them to finish 'researching'. In the meantime, we figured out a solution... Still testing it, but results so far are favorable! Figured I'd share. Any feedback is appreciated.
So we have all the specific triggers with their variables set up in rich client, and created the SQL Procs for each individual bonus program, just the way we laid out in the prior post.
We created an additional shell procedure with one parameter (BonusType). Inside that proc we build an execute query string and call sp_executesql.
All we have to do in the flow is pull the trigger value, assign it to a variable, and provide it as the parameter to the shell proc below.
--------------------------------------------------------------------------------------------
create procedure lm_ghr.spBonusShell @BonusType as NVARCHAR AS BEGIN SET NOCOUNT ON; DECLARE @queryToRun NVARCHAR(128) = 'EXECUTE lm_ghr.spBonus' + @BonusType; EXECUTE sp_executesql @queryToRun; END
If we need to add a bonus program, we can just create the process trigger with the BonusType variable, and create the SQL proc it needs to run. As long as all the bonus SQL statements return data in a consistent layout, the main flow doesn't need to be touched.