At times it is desired that if a particular concurrent
program scheduled to run at a particular time interval could be changed from
running at their scheduled interval. This happens generally when
(i) System resources acquired by the first program
are also required by a second program.
For eg. Program A is scheduled to run every 10 minutes and Program B is
scheduled to run once a day at 6:00 am. It is desired Program A should not run between
6:00 am to 7:00 am when Program B is running.
(ii) There is
no activity going on.
For eg. Program A is scheduled to run every 10 minutes, but there is no
point in running the program between 8pm and 6:00 am every day or on
Saturday/Sunday.
(iii) Change in frequency of the concurrent program
For e.g, Program A runs every hour, it is desired to run Program A at
every 15 minutes between 10 am and 11 am every day.
Oracle Scheduling Logic
Whenever a concurrent program is
scheduled to run periodically in R12 or 11i, Oracle creates a record in fnd_conc_release_classes
table. CLASS_INFO column contains the period interval. Simultaneously it
creates another record in fnd_concurrent_requests table. RESUBMIT_INTERVAL column of the latest record
in fnd_concurrent_requests for the Schedule determines at what interval the
concurrent program is going to run next.
Concurrent Program Setup
All components required to setup a
concurrent program to change resubmit interval of any Schedule in Oracle
e-business suite (R12 and 11i) in 6 simple steps is given below.
1.
Create Package Header
CREATE OR REPLACE PACKAGE BPL_CHANGE_REQUEST_INTERVAL
IS
PROCEDURE update_conc_request_interval
(
errbuff OUT VARCHAR2,
retcode OUT VARCHAR2,
P_RELEASE_CLASS IN VARCHAR2,
P_INTERVAL
IN NUMBER );
END BPL_CHANGE_REQUEST_INTERVAL;
/
2.
Create Package Body
CREATE OR REPLACE PACKAGE BODY
BPL_CHANGE_REQUEST_INTERVAL
IS
PROCEDURE update_conc_request_interval
(
errbuff OUT VARCHAR2,
retcode
OUT VARCHAR2,
P_RELEASE_CLASS IN
VARCHAR2,
P_INTERVAL IN NUMBER)
is
CURSOR c_rel_class is
select c.release_class_id from
fnd_conc_release_classes c
where RELEASE_CLASS_NAME = P_RELEASE_CLASS;
l_REQUEST_ID number;
l_LAST_UPDATE_DATE varchar2(50);
l_PHASE_CODE varchar2(2);
l_STATUS_CODE varchar2(2);
l_RESUBMIT_INTERVAL number;
l_RESUBMIT_INTERVAL_UNIT_CODE varchar2(50);
l_ACTUAL_START_DATE varchar2(50);
l_ACTUAL_COMPLETION_DATE varchar2(50);
l_COMPLETION_TEXT varchar2(100);
l_REQUEST_ID2 number:=
NULL;
l_RESUBMIT_INTERVAL2 number := NULL;
l_CLASS_INFO varchar2(50);
Begin
Fnd_File.put_line(Fnd_File.LOG,
'BPL_CHANGE_REQUEST_INTERVEL.upd_conc_request_interval' || ' start
at : ' || to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
Fnd_File.put_line(Fnd_File.LOG, 'PARAMETER : Release Class : '||
P_RELEASE_CLASS);
Fnd_File.put_line(Fnd_File.LOG, 'PARAMETER : Resubmit Interval : '||
to_char(P_INTERVAL));
for r_rel_class in c_rel_class
loop
select
cr.REQUEST_ID
, to_char(cr.LAST_UPDATE_DATE,'DD-MON-YYYY
HH24:MI:SS')
, cr.PHASE_CODE
, cr.STATUS_CODE
, cr.RESUBMIT_INTERVAL
, cr.RESUBMIT_INTERVAL_UNIT_CODE
, to_char(cr.ACTUAL_START_DATE,'DD-MON-YYYY
HH24:MI:SS')
, to_char(
cr.ACTUAL_COMPLETION_DATE,'DD-MON-YYYY HH24:MI:SS')
, cr.COMPLETION_TEXT
INTO
l_REQUEST_ID
, l_LAST_UPDATE_DATE
, l_PHASE_CODE
, l_STATUS_CODE
, l_RESUBMIT_INTERVAL
, l_RESUBMIT_INTERVAL_UNIT_CODE
, l_ACTUAL_START_DATE
, l_ACTUAL_COMPLETION_DATE
, l_COMPLETION_TEXT
from
fnd_concurrent_requests cr
where
cr.release_class_id=
r_rel_class.release_class_id
and
last_update_date >= (select max(last_update_date) from fnd_concurrent_requests
r2
where r2.RELEASE_CLASS_ID =
r_rel_class.release_class_id )
and
cr.REQUEST_ID = (select max(r3.REQUEST_ID) from fnd_concurrent_requests r3
where r3.RELEASE_CLASS_ID =
r_rel_class.release_class_id);
Fnd_File.put_line(Fnd_File.LOG,
'Before Update');
Fnd_File.put_line(Fnd_File.LOG,
'------------------------');
Fnd_File.put_line(Fnd_File.LOG,
'REQUEST_ID : '|| to_char(l_REQUEST_ID));
Fnd_File.put_line(Fnd_File.LOG,
'LAST_UPDATE_DATE : ' ||l_LAST_UPDATE_DATE);
Fnd_File.put_line(Fnd_File.LOG,
'PHASE_CODE : '
||l_PHASE_CODE);
Fnd_File.put_line(Fnd_File.LOG,
'STATUS_CODE : ' ||
l_STATUS_CODE );
Fnd_File.put_line(Fnd_File.LOG,
'RESUBMIT_INTERVAL : '|| to_char(l_RESUBMIT_INTERVAL));
Fnd_File.put_line(Fnd_File.LOG,
'RESUBMIT_INTERVAL_UNIT_CODE : ' || l_RESUBMIT_INTERVAL_UNIT_CODE);
Fnd_File.put_line(Fnd_File.LOG,
'ACTUAL_START_DATE : '||
l_ACTUAL_START_DATE);
Fnd_File.put_line(Fnd_File.LOG,
'ACTUAL_COMPLETION_DATE : ' || l_ACTUAL_COMPLETION_DATE);
Fnd_File.put_line(Fnd_File.LOG,
'COMPLETION_TEXT : '|| l_COMPLETION_TEXT);
if
NVL(P_INTERVAL,0) > 0 then
begin --begin1
select
CLASS_INFO
INTO l_CLASS_INFO
from
fnd_conc_release_classes crc
where
crc.RELEASE_CLASS_ID = r_rel_class.release_class_id;
Fnd_File.put_line(Fnd_File.LOG,
'Release Class INFO: '|| l_CLASS_INFO);
update
fnd_conc_release_classes crc
set
crc.CLASS_INFO = CONCAT(P_INTERVAL,SUBSTR(CLASS_INFO,
length(CLASS_INFO)-3))
/*
replace(crc.CLASS_INFO,l_RESUBMIT_INTERVAL,P_INTERVAL) */
where
crc.release_class_id = r_rel_class.release_class_id;
/*
Replace function creates issue sometimes */
select
CLASS_INFO
INTO l_CLASS_INFO
from
fnd_conc_release_classes crc
where
crc.RELEASE_CLASS_ID = r_rel_class.release_class_id;
Fnd_File.put_line(Fnd_File.LOG,
'Release Class INFO updated to ' || l_CLASS_INFO );
exception
when
others then
Fnd_File.put_line(Fnd_File.LOG,
'Error in updating fnd_conc_release_classes');
Fnd_File.put_line(Fnd_File.LOG,
sqlerrm);
end; --begin1
begin --begin2
update
fnd_concurrent_requests cr
set
cr.RESUBMIT_INTERVAL =P_INTERVAL
where cr.RELEASE_CLASS_ID =
r_rel_class.release_class_id
and
cr.RESUBMIT_INTERVAL = l_RESUBMIT_INTERVAL
and
last_update_date >= (select max(last_update_date) from
fnd_concurrent_requests r2
where r2.RELEASE_CLASS_ID =
r_rel_class.release_class_id );
commit;
Fnd_File.put_line(Fnd_File.LOG,
'updated fnd_concurrent_requests');
exception
when
others then
Fnd_File.put_line(Fnd_File.LOG,
'1. Error in updating fnd_concurrent_requests ');
Fnd_File.put_line(Fnd_File.LOG,
sqlerrm);
end; --begin2
begin ----begin3
select cr.REQUEST_ID
,cr.RESUBMIT_INTERVAL
INTO
l_REQUEST_ID2
,l_RESUBMIT_INTERVAL2
from
fnd_concurrent_requests cr
where
cr.release_class_id =
r_rel_class.release_class_id
and
cr.RESUBMIT_INTERVAL =
l_RESUBMIT_INTERVAL
and
cr.REQUEST_ID = (select max(cr2.REQUEST_ID) from
fnd_concurrent_requests cr2
where
cr2.release_class_id=
r_rel_class.release_class_id);
/*
Trying to find out if the latest request id is having old RESUBMIT INTERVAL.
Most cases it will return null */
/*
Above condition occurs only if new record was created (status P,I) at the same
time it updated current latest record(phase code R)*/
exception
when
others then
Fnd_File.put_line(Fnd_File.LOG,
'Select statement didnot fetch. NO_DATA_FOUND is ok ');
Fnd_File.put_line(Fnd_File.LOG,
sqlerrm);
end; ----begin3
if
l_REQUEST_ID2 is not null
then
Fnd_File.put_line(Fnd_File.LOG,
'Request Id ' || to_char(l_REQUEST_ID2) || ', RESUBMIT_INTERVAL2 ' ||
to_char(l_RESUBMIT_INTERVAL2));
end
if;
if
l_RESUBMIT_INTERVAL2 <> P_INTERVAL
then
/* update once again */
Fnd_File.put_line(Fnd_File.LOG,
'l_RESUBMIT_INTERVAL2' || to_char(l_RESUBMIT_INTERVAL2) || ' <>
P_INTERVAL'|| to_char(P_INTERVAL) );
Fnd_File.put_line(Fnd_File.LOG,
'updating again');
begin
----begin4
update
fnd_concurrent_requests cr
set
cr.RESUBMIT_INTERVAL =P_INTERVAL
where cr.RELEASE_CLASS_ID =
r_rel_class.release_class_id
and
last_update_date >= (select max(last_update_date) from
fnd_concurrent_requests r2
where r2.RELEASE_CLASS_ID =
r_rel_class.release_class_id );
commit;
exception
when
others then
Fnd_File.put_line(Fnd_File.LOG,
'2. Error in updating fnd_concurrent_requests ');
Fnd_File.put_line(Fnd_File.LOG,
sqlerrm);
end;
---begin 4
end
if;
select
cr.REQUEST_ID
, to_char(cr.LAST_UPDATE_DATE,'DD-MON-YYYY
HH24:MI:SS')
, cr.PHASE_CODE
, cr.STATUS_CODE
, cr.RESUBMIT_INTERVAL
, cr.RESUBMIT_INTERVAL_UNIT_CODE
, to_char(cr.ACTUAL_START_DATE,'DD-MON-YYYY
HH24:MI:SS')
, to_char(
cr.ACTUAL_COMPLETION_DATE,'DD-MON-YYYY HH24:MI:SS')
, cr.COMPLETION_TEXT
INTO
l_REQUEST_ID
,l_LAST_UPDATE_DATE
,l_PHASE_CODE
,l_STATUS_CODE
,l_RESUBMIT_INTERVAL
,l_RESUBMIT_INTERVAL_UNIT_CODE
,l_ACTUAL_START_DATE
,l_ACTUAL_COMPLETION_DATE
,l_COMPLETION_TEXT
from
fnd_concurrent_requests cr
where
cr.release_class_id=
r_rel_class.release_class_id
and
last_update_date >= (select max(last_update_date) from
fnd_concurrent_requests r2
where r2.RELEASE_CLASS_ID =
r_rel_class.release_class_id )
and
cr.REQUEST_ID = (select max(r3.REQUEST_ID) from fnd_concurrent_requests r3
where r3.RELEASE_CLASS_ID =
r_rel_class.release_class_id);
Fnd_File.put_line(Fnd_File.LOG,
'After Update');
Fnd_File.put_line(Fnd_File.LOG,
'------------------------');
Fnd_File.put_line(Fnd_File.LOG,
'REQUEST_ID : '|| to_char(l_REQUEST_ID));
Fnd_File.put_line(Fnd_File.LOG,
'LAST_UPDATE_DATE : ' ||l_LAST_UPDATE_DATE);
Fnd_File.put_line(Fnd_File.LOG,
'PHASE_CODE : '
||l_PHASE_CODE);
Fnd_File.put_line(Fnd_File.LOG,
'STATUS_CODE : ' ||
l_STATUS_CODE );
Fnd_File.put_line(Fnd_File.LOG,
'RESUBMIT_INTERVAL : '|| to_char(l_RESUBMIT_INTERVAL));
Fnd_File.put_line(Fnd_File.LOG,
'RESUBMIT_INTERVAL_UNIT_CODE : ' ||l_RESUBMIT_INTERVAL_UNIT_CODE);
Fnd_File.put_line(Fnd_File.LOG,
'ACTUAL_START_DATE : '||
l_ACTUAL_START_DATE);
Fnd_File.put_line(Fnd_File.LOG,
'ACTUAL_COMPLETION_DATE : ' || l_ACTUAL_COMPLETION_DATE);
Fnd_File.put_line(Fnd_File.LOG,
'COMPLETION_TEXT : '|| l_COMPLETION_TEXT);
end if; --
NVL(P_INTERVAL,0) > 0
end loop;
END
update_conc_request_interval;
END BPL_CHANGE_REQUEST_INTERVAL;
/
3.
Create Concurrent Program Executable
4.
Create Concurrent Program 
5.
Create Concurrent Program Parameters
6.
Add Concurrent Program to Request Group
Add the newly
created concurrent program to a request group associated with your
responsibility.
You are ready to
re-schedule any concurrent program now.
Concurrent Program takes two parameters
1. Release Class Name: This is unique to every concurrent program
schedule . This information can be retrieved from fnd_conc_release_classes table.
2. ReSubmit Interval: The new Interval for the Schedule.
Every concurrent program schedule has a re-submit
interval and it is as per this value the actual start time of next concurrent
request is scheduled. For eg. If a concurrent program schedule has a re-submit
interval of 10 minutes and we want to
change the resubmit interval to 60 minutes, this parameter is passed as 60.
Assumptions
The following assumptions have been made:
·
User has basic knowledge of
tables fnd_concurrent_requests and fnd_conc_release_classes tables.
·
User has the knowledge to find
the release_class_name for the concurrent program schedule for which resubmit
interval needs to be changed
·
User is aware that this program
can only be used to change resubmit intervals of Periodic scheduling of concurrent
programs. ( Changing ‘On Specific Day’ scheduling is not in the current scope)
Program Logic
The program updates the RESUBMIT_INTERVAL
column of the latest record in fnd_concurrent_requests table for the release
class passed as the parameter. It also updates the CLASS_INFO column of fnd_conc_release_classes
table which helps user to find the interval time of the schedule through front
end Applications as well.
Rarely does it happen that when the
latest record of fnd_concurrent_requests is updated, at the same moment it creates
the next record. This would leave the schedule to continue with the previous
resubmit interval which is not desired. In order to overcome the issue the
program logic immediately checks if the latest record is having the new
resubmit interval and if not, the latest record will be updated again.
Sample SQL
select rc. RELEASE_CLASS_NAME,rc.CLASS_INFO, rc.* from fnd_conc_release_classes rc where rc.RELEASE_CLASS_ID = 9999999;
RELEASE_CLASS_ID will be populated in fnd_concurrent_requests table whenever a concurrent program is scheduled.
If you have any comments or queries, you are most welcome -- Biju Plassery
No comments:
Post a Comment