I am little stuck in one logic. Don't seem to be a complecated but just looking for the right approach.
I have a custom concurrent program which has a SQL query which returns multiple rows. Each row is a for each supplier e.g.
Supplier1 email@example.com 123 5000
Supplier2 firstname.lastname@example.org 456 4000
Supplier3 email@example.com 789 3000
This set is returned based upon some criteria mentioned in the where clause of the SQL.
Ultimate aim is to send 1 notification to each of the email id's and those notification should have the corresponding attribute values.
3 notifications fired from a workflow triggered by the concurrent program (which actually returns this 3 rows in a SQL)
Supplier 1 to receive 5000
Supplier2 to receive 4000
Supplier3 to receive 3000
My approach / or the only approach coming in mind is using a cursor for the SQL inside the concurrent program and using a for loop, initiating the workflow each time for a loop iteration i.e for each iteration of loop , workflow procedure will need to be initiated, so item type will be same but item key will of 3 different item key. Please let me know if this is the only approach or is it somehow possible to fire only one instance of workflow and not 3 different instances to send 3 notifications.
Ad hoc role creation would not be option here because the number of rows may be large and not just 10-15. I just need the correct approach to handle this,