It’s quite an interesting way that’s used to implement concurrent request schedules in Oracle Applications. In this post I’ll describe how “Periodic” and “On Specific Day” type of schedules are stored in the Database and if you will be patient enough to read
all the story, I’ll give you a query that can be used to report all the request schedules in the environment.
I’ll pay attention to the repeating schedules only as requests submitted for one-time execution are not too interesting, basically, they
use the fnd_concurrent_requests.requested_start_date field to store the time the request has to be executed. This field is used in repeating schedules
to specify the time of next execution.
There are just 2 normal types of repeating schedules.
Defining a ‘periodic’ schedule
Periodic – the request is submitted for execution in pre-defined intervals of specified number of months, weeks, days, hours or minutes.
Definig a ‘on Specific Days’ schedule
On Specific Days – user can choose exact dates of month or days of week when the request will be executed. There’s an option to schedule requests for the last day of months too.
There’s also a 3rd option – Advanced – this is a special option that implements possibility to run requests based on schedules, that depend on different financial calendars and financial periods. This kind of scheduling is rarely used as it’s available in few
Applications products only, e.g. GL. These schedules are managed by Scheduler/Prereleaser Concurrent Manager.
There are 2 tables invoved in storing the information about schedules: fnd_concurrent_requests that stores all basic information about concurrent requests
including the next time the request is scheduled for, andfnd_conc_release_classes that stores information about the schedules. Both tables can be joined
The fields we are interested in are:
fnd_conc_release_classes.CLASS_TYPE – contains value ‘P’ for “Periodic” schedules, “S” – for “on Specific Days” schedules and “X” – for advanced schedules.
fnd_conc_release_classes.DATE1 – start date of the schedule (“Start at” field in the form)
fnd_conc_release_classes.DATE2 – end date of the schedule (“End at” field in the form) – this information is doubled in fnd_concurrent_requests.resubmit_end_date.
fnd_conc_release_classes.CLASS_INFO – this is the most interesting field as it contains all the information needed for rescheduling. The
format of the field depends on the type of schedule.
In case of Periodic schedule fnd_conc_release_classes.CLASS_INFO field
contains values like “2:D:S” orX:Y:Z where:
X – number of months/weeks/days/hours/minutes the request has to be rescheduled from prior run.
Y – contains a single letter representing units
“M” – months;
“D” – days;
“H” – hours;
“N” – minutes;
(there is no representation of “weeks” option. If you specify interval in weeks, it’s automatically calculated and stored in “days”).
Z – contains a single letter to represent if the rescheduling has to be done from start or from completion of the prior run
S – from the start of the prior run;
C – from the completion of the prior run.
30:N:S – Repeat every 30 minutes from the start of the prior run
5:N:C – Repeat every 5 minutes from the completion of the prior run
12:H:S – Repeat every 12 hours from the start of the prior run
It’s interesting that information about intervals of periodic schedules is duplicated in fnd_concurrent_requeststable fields RESUBMIT_INTERVAL,
RESUBMIT_INTERVAL_TYPE_CODE andRESUBMIT_INTERVAL_UNIT_CODE. I haven’t yet found why’s that so.
“ON SPECIFIC DAY” schedule
In case of on Specific Day schedule fnd_conc_release_classes.CLASS_INFO field
contains values like “000010000000000000000000000000010000000” – a 39 character value consisting of 0 and 1.
The idea is that the placement of 1-s represent the options selected through form:
1-s at places 1 to 31 – represent dates, when request has to be run, eg, if the 10th character is “1” – the request is scheduled to run on 10th day of
character “1” at the 32nd position – specifies that the request has to be run at the last day of each month;
1-s at places 33 to 39 – specifies days of week (Sunday – Saturday)the request has to be run. if the 33rd character is “1” – the request is scheduled to
run each Sunday, if 34th – on Monday and so on.
000000000000000000000000000000000000001 – Days of week: Sa
111111111000000000000000000000000111110 – Dates: 1 2 3 4 5 6 7 8 9. Days of week: Mo Tu We Th Fr
000000000000000000000000000000010000000 – Last day of month
Reporting all schedules
Using the information given above I wrote a “small” query to provide a report like this:
Reporting all schedules
The query was tested in Oracle Applications versions 11.5.9, 220.127.116.11, 12.0.1 and 12.0.4 so there’s a good chance it will work on others as well. The performance of the query was OK. but I have to say – test/analyze it before running in productio.
I know there are some interesting lines in this query that might need some explanation, but as that is not directly connected to the topic of this post I’ll provide explanation in comments if you will ask for it.