Siebel: Querying for Distinct Records

Many a time during Siebel configuration, one is left wondering "so near yet so far" - to SQL that is. A recent scenario had this problem - send alerts to the activity's account's team members when it is past the due date. Sending more than one email for one or more pending activity is
not acceptable. One of the common things that we do when confronted with this problem is to just write a workflow or a piece of code somewhere that can:

1. Query for all activities past the due date


2. For each activity, get the account and query for the
   account's position team


3. For each member of the team, get the corresponding primary
   employee


4. Send notification/alerts and flag him to be ignored in
   future

Looking at this problem from a different angle, the solution
is somewhat more simple - get unique "account team member" - "activity"
combination that will be the potential notification candidate. Siebel
provides a "Distinct" property on business components that helps us to
achieve just this. Note that this property enforces a "DISTINCT" clause
on all fields present in the BC, which may force us to create a clone
when needed. Let us re-look at the solution now:

1. Create a BC based on S_EVT_ACT and with the following
   fields:
• Due Date (DUE)
• Status (EVT_STAT_CD)
• Account Id (OU_ID)
• Account Primary Position Id (S_ACCNT_POSTN.POSITION_ID)
• Join: S_EVT_ACT.OU_ID = S_ACCNT_POSTN.OU_EXT_ID)
• Account Primary Employee Email (S_CONTACT.EMAIL_ADDR)
• Join: S_ACCNT_POSTN.POSITION_ID = S_POSTN.PAR_ROW_ID
• Join: S_POSTN.PR_EMP_ID = S_CONTACT.PAR_ROW_ID


2. Enable "Distinct" flag on this BC


3. Create a simple workflow based on a business object which
   has the above BC as primary and which leverages the ""Outbound
   Communications Manager" service


4. Create a "Repeating Component Request" with the required
   search criteria - "[Due] = Today() AND [Status] = 'Open'"

Well, how is that? Of course both have the same number of steps and the second one actually looks more pronounced. But if you have done a fair bit of coding and solutions to problems such as these you know what to choose.