Many times we reverse engineer the code in Oracle Apps when troubleshooting an issue or in an attempt to gain in-depth knowledge of the inner working of a module/functionality. In the event of doing so, we unearth the design methodologies implemented by the specific programmer in Oracle product development team. With time we learn that each product development team in Oracle Apps has their own unique style and mindset that is reflected in the design approach of respective module. Being consultant, we are able to grasp the ideas from various design/methodologies of thousands of Oracle product development team members. These tricks/ideas then inspire us when it comes to developing our own extensions. Of course the beauty of being a consultant is that we are able to cherry pick the ideas from the product, those ideas which are elegant from our perspective.
Anyways, coming back to the point, this article is about another one of those ideas that was inspired by Oracle product itself. Let us say your requirement is that when certain event occurs, you then wish to raise yet another event that executes at a specific point in time/hour/minute in future. Of course there are various design approaches you can take, one of which could be to use workflow engine activity with some kind of deferred logic. Another approach could be to schedule a concurrent process that dequeues some table for pending transactions as per their timestamp. This approach is not ideal because firstly it gives no visibility from the User Interface as to when what will be executed, and secondly you are programming for a feature that Oracle Apps gives out of the box. Using DBMS_JOBS is another option, but that too does not give the visiblity of the queue from any screen in apps.
Therefore my preference is to use fnd_request.submit_request with the start_time parameter being passed in. The beauty of this approach is that standard functionality of concurrent manager takes care of processing your API at a specific point in time. Not only that, with this solution you are also able to monitor the Pending/Scheduled process displaying the timestamp of when those will be executed. Of course, you will pass some kind of unique id as a parameter to this custom concurrent program, so that just the desired transaction gets processed at a specific point in time. The descrpition parameter can be populated with a text that user can identify with the underlying transaction. The quick sample code is....
n_request_id := fnd_request.submit_request
(
application => 'XXFT',
program => 'XXFTCREATEORDER',
description => '<description of specific trx>',
start_time => sysdate + 4.3 ,
sub_request => FALSE,
argument1 => n_order_id
) ;
The source of this idea comes from Talent Management module in Self Service HRMS. During the objective setting process you create and publish a plan. The period of time between the start date and end date of the plan is called the "planning period" for appraisals/objectives. After the end of planning phase, the objective scorecards get converted into Appraisals. The creation of appraisals happen at a specific point in time in future, i.e. after than plan end date. Therefore Oracle submits a concurrent request with deferred start_time parameter to convert the ScorecardId into an Appraisal. The concurrent program remains pending until the end of planning phase for that published plan. This is a simple but very useful technique for derring the processing to a specific point in time in future.
Apps2Fusion Articles 
Comments
Thanks again for a innovative idea and solution. Your sample code is as below. Now do u mean that we will typically execute this in our pl/sql code after checking that any earlier request completed or failed and based on its completion status, below will be called ? Your statement "The concurrent program remains pending
until the end of planning phase for that published plan. " seems to suggest the same.
n_requ est_id := fnd_request.sub mit_request
(
application => 'XXFT',
program => 'XXFTCREATEORDE R',
description => '',
start_time => sysdate + 4.3 ,
sub_request => FALSE,
argument1 => n_order_id
) ;
Thanks, Vishal.
I have implemented the same logic mentioned above in my code.But the problem is the cocurrent program which is called in this API is never been get released by concurrent manager.It's always showing Pending/Standby status.Bcoz of this, the main concurrent program which calls this API is in Running/Running status.So then i dropped of this logic to implement.Can u plz suggest why it has happend like dat ??
Thanks,
P rathy
The number of processes in pending queue will not impact the running queue
Thanka,
a nil
Thanks for ur reply. But the process which was in pending queue called from another process.Logical ly ,in this case once the pending queue process gets release from the concurrent manager (complete status) then only the calling process wil goto completed status.This was not happened in my case.
Thanks ,
Prathy
RSS feed for comments to this post