Concurrent Program Business Event Hook - How to Get It Work Properly

Since R12 concurrent program can invoke business processes during different phases of program execution. I'd done similar customization in version 11.5 and now this is a out-of-the-box feature. The problem about this feature is that:
How can I invoke it? And more importantly, how can I invoke specific logic for a given concurrent program only?

(1) First and foremost, set the value of the mysterious Profile Option "Concurrent: Business Intelligence Integration Enable" to "Yes". (Can anyone from Oracle tell me why it is called this name?)

(2) Check one or more of the business events for a given concurrent program.

(3) Do a lookup for the Even Name

Concurrent Program Business Event	Event Definition Name
Program Completed	oracle.apps.fnd.concurrent.program.completed
Request Completed	oracle.apps.fnd.concurrent.request.completed
Request On Hold	oracle.apps.fnd.concurrent.request.on_hold
Post Processing Ended	oracle.apps.fnd.concurrent.request.postprocessing_ended
Post Processing Started	oracle.apps.fnd.concurrent.request.postprocessing_started
Request Resumed	oracle.apps.fnd.concurrent.request.resumed
Request Running	oracle.apps.fnd.concurrent.request.running
Request Submitted	oracle.apps.fnd.concurrent.request.submitted

(4) Administrator Workflow => Business Events => Do a search of this even name

(5) Click "Subscription", then "Create Subscription".  Create a Custom Action Type

(6) Set the PL/SQL Rule Function (see below for sample code for this function). Save the settings for this subscription.

(7) When this concurrent program is executed and it reaches certain status/phase, it will call the associated business event, and trigger to start the event subscription (it can be deferred if the phase value is higher than 99).  In our case it will run the PL/SQL Rule function (must be a function inside a package).  Here is how this function looks like:

FUNCTION REQUEST_COMPLETED_FUNC (
   p_subscription_guid   IN       RAW,
   p_event               IN OUT   WF_EVENT_T) RETURN VARCHAR2 IS

var_programName      VARCHAR2(100);
var_appShortName     VARCHAR2(30);
var_respKey          VARCHAR2(100);
var_userName         VARCHAR2(30);
num_concRequestID    NUMBER;

BEGIN

num_concRequestID := p_event.getValueForParameter('REQUEST_ID');

select fcp.concurrent_program_name
     , fa.application_short_name
     , fr.responsibility_key
     , fu.user_name
  into var_programName
     , var_appShortName
     , var_respKey
     , var_userName
  from fnd_concurrent_requests fcr
     , fnd_concurrent_programs fcp
     , fnd_application fa
     , fnd_responsibility fr
     , fnd_user fu
 where fcr.request_id             = num_concRequestID 
   and fcr.concurrent_program_id  = fcp.concurrent_program_id
   and fcr.program_application_id = fa.application_id
   and fcr.responsibility_id      = fr.responsibility_id
   and fcr.requested_by           = fu.user_id;

IF var_programName  = 'XXX' and 
   var_appShortName = 'YY' and
   var_respKey      = 'ZZZ' and
   var_userName     = 'TTT' then

/* Put your program/responsibility/user-specific logic in here */

END IF;
END;

Possible parameters you can get from P_EVENT:
REQUEST_ID
REQUESTED_BY
PROGRAM_APPLICATION_ID
CONCURRENT_PROGRAM_ID
STATUS
COMPLETION_TEXT
TIME_STAMP  (in form of DDMMYY HHMISS)

In addition, you can retrieve info for this Workflow Event Type through standard methods, e.g. getEventKey, getEventName, getEventData, etc.

Basically having the REQUEST ID is good enough to query up all different kinds of parameters associated with this request.

Event subscription is a generic mechanism, so every concurrent program which has certain business event active will trigger the subscription to start.  So MAKE SURE you have implement highly-restrained conditions and very specific logic in the Rule Function so that the code is executed for desired situation only.

Okay.  Here is another mystery: where is the info of concurrent program business event stored?
Answer: Table FND_CONC_PROG_ONSITE_INFO, column BUSINESS_EVENT_MAP
It is a 50 character column with Y or N in there, and only the first 8 characters are relevant to the business event setting for a given concurrent_program_id.
Business Event Position relative to the character column position:
1 4 7
2 5 8
3 6

So, if you have Request Submitted and Request Completed checked, the column value becomes:
YNNNNNNYNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN

Genius, isn't it?