EBS 并发请求 计划 fnd_conc_release_classes
EBS并发请求中有可以设置计划用以定时运行，计划存储在 fnd_conc_release_classes 表中，通过release_class_id与请求表fnd_concurrent_requests关联。
详细信息如下：
RELEASE_CLASS_ID: 主键，与fnd_concurrent_requests关联.
CLASS_TYPE: contains value 'P' for "Periodic" schedules, 'S' for "on Specific Days" schedules and 'X' for "advanced schedules". P:定期，S:在特定日期，X:高级
DATE1: start date of the schedule ("Start at" field in the form) 起始日期
DATE2: end date of the schedule ("End at" field in the form), as fnd_concurrent_requests.resubmit_end_date 终止日期
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. 因计划类型的不同，数据格式也随之出现差异.
"PERIODIC" schedule: 定期
In case of Periodic schedule fnd_conc_release_classes.CLASS_INFO field contains values like "2:D:S" or X: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.
Some samples:
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 infnd_concurrent_requests table fields RESUBMIT_INTERVAL, RESUBMIT_INTERVAL_TYPE_CODE and RESUBMIT_INTERVAL_UNIT_CODE.
"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 each month;
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.
Some samples:
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
最后，奉上一段SQL:
SELECT r.Request_Id, p.User_Concurrent_Program_Name || CASE WHEN p.User_Concurrent_Program_Name = 'Report Set' THEN (SELECT ' - ' || s.User_Request_Set_Name FROM Fnd_Request_Sets_Tl s WHERE s.Application_Id = r.Argument1 AND s.Request_Set_Id = r.Argument2 AND LANGUAGE = 'US') WHEN p.User_Concurrent_Program_Name = 'Check Periodic Alert' THEN (SELECT ' - ' || a.Alert_Name FROM Alr_Alerts a WHERE a.Application_Id = r.Argument1 AND a.Alert_Id = r.Argument2 AND LANGUAGE = 'US') END Concurrent_Program_Name, CASE WHEN p.User_Concurrent_Program_Name != 'Report Set' AND p.User_Concurrent_Program_Name != 'Check Periodic Alert' THEN r.Argument_Text END Argument_Text, r.Requested_Start_Date Next_Run, r.Hold_Flag On_Hold, Decode(c.Class_Type, 'P', 'Periodic', 'S', 'On Specific Days', 'X', 'Advanced', c.Class_Type) Schedule_Type, CASE WHEN c.Class_Type = 'P' THEN 'Repeat every ' || Substr(c.Class_Info, 1, Instr(c.Class_Info, ':') - 1) || Decode(Substr(c.Class_Info, Instr(c.Class_Info, ':', 1, 1) + 1, 1), 'N', ' minutes', 'M', ' months', 'H', ' hours', 'D', ' days') || Decode(Substr(c.Class_Info, Instr(c.Class_Info, ':', 1, 2) + 1, 1), 'S', ' from the start of the prior run', 'C', ' from the completion of the prior run') WHEN c.Class_Type = 'S' THEN Nvl2(Dates.Dates, 'Dates: ' || Dates.Dates || '. ', NULL) || Decode(Substr(c.Class_Info, 32, 1), '1', 'Last day of month ') || Decode(Sign(To_Number(Substr(c.Class_Info, 33))), '1', 'Days of week: ' || Decode(Substr(c.Class_Info, 33, 1), '1', 'Su ') || Decode(Substr(c.Class_Info, 34, 1), '1', 'Mo ') || Decode(Substr(c.Class_Info, 35, 1), '1', 'Tu ') || Decode(Substr(c.Class_Info, 36, 1), '1', 'We ') || Decode(Substr(c.Class_Info, 37, 1), '1', 'Th ') || Decode(Substr(c.Class_Info, 38, 1), '1', 'Fr ') || Decode(Substr(c.Class_Info, 39, 1), '1', 'Sa ')) END Schedule, c.Date1 Start_Date, c.Date2 End_Date, c.Class_Info FROM Fnd_Concurrent_Requests r, Fnd_Conc_Release_Classes c, Fnd_Concurrent_Programs_Tl p, (SELECT Release_Class_Id, Substr(MAX(Sys_Connect_By_Path(s, ' ')), 2) Dates FROM (SELECT Release_Class_Id, Rank() Over(PARTITION BY Release_Class_Id ORDER BY s) a, s FROM (SELECT c.Class_Info, l, c.Release_Class_Id, Decode(Substr(c.Class_Info, l, 1), '1', To_Char(l)) s FROM (SELECT LEVEL l FROM Dual CONNECT BY LEVEL <= 31), Fnd_Conc_Release_Classes c WHERE c.Class_Type = 'S') WHERE s IS NOT NULL) CONNECT BY PRIOR (a || Release_Class_Id) = (a - 1) || Release_Class_Id START WITH a = 1 GROUP BY Release_Class_Id) Dates WHERE r.Phase_Code = 'P' AND c.Application_Id = r.Release_Class_App_Id AND c.Release_Class_Id = r.Release_Class_Id AND Nvl(c.Date2, SYSDATE + 1) > SYSDATE AND c.Class_Type IS NOT NULL AND p.Concurrent_Program_Id = r.Concurrent_Program_Id AND p.Application_Id = r.Program_Application_Id AND p.Language = 'US' AND Dates.Release_Class_Id(+) = r.Release_Class_Id ORDER BY On_Hold, Next_Run;
