1 package body FND_CONC_RELEASE_CLASS_UTILS as
2 /* $Header: AFCPCRCB.pls 120.2.12010000.2 2009/11/24 22:04:12 jtoruno ship $ */
3
4
5
6
7 -- Name
8 -- calc_specific_startdate
9 -- Purpose
10 -- Given a requested start date and the class info for a Specific schedule,
11 -- return the next date that a request should run according to this schedule.
12 -- May return null if no valid date found.
13 --
14 function calc_specific_startdate(req_sdate in date,
15 class_info in varchar2) return date is
16
17 found number;
18 offset number;
19 temp_date date;
20 dow number; -- day of week 1 for Sun, 7 for Sat
21 dom number; -- day of month between 1 to 31
22 nextdom number;
23 weekno number; -- no of week in month
24 moy number; -- month of year between 1 to 12
25 datespec boolean; -- true if some date is specified
26 dayspec boolean; -- true if some week day is specified
27 begin
28 found := 0;
29 offset := 0;
30
31 /* find first schedule time after sysdate then subtract a day */
32
33 temp_date := req_sdate + floor(trunc(sysdate - req_sdate));
34
35 -- handle 56 bits code for advance scheduling
36 if (LENGTH(class_info) = 56) then
37
38 -- check if any of date is specified
39 -- ie if either of bit from 1 to 32 is set
40 if (SUBSTR(class_info, 1, 32) = 0) then
41 datespec := false;
42 else
43 datespec := true;
44 end if;
45
46 -- check if any of the day is specified
47 -- ie if either of bit from 33 to 39 is set
48 if (SUBSTR(class_info, 33, 7) = 0) then
49 dayspec := false;
50 else
51 dayspec := true;
52 end if;
53 end if;
54
55
56 /* find next day where day of week or month is executable.
57 Day of week complexity is to avoid problems with calendars
58 where weekday numbering is different. Nextdom is for finding
59 if its the last day of the month. Class info in this case is a
60 bitfield: first 31 are for dom, 32 is last dom, 33-39 is dow */
61
62 /* 8590269 - Advanced Sched for dates within a year of the current date were
63 not properly getting calculated since loop only went for 31 days from
64 current day. offset is now avail for a full year from current date. */
65
66 while (offset < 365) and (found = 0) loop
67 offset := offset + 1;
68 dow := MOD(trunc(TO_NUMBER(TO_CHAR(temp_date+1+offset,'J'))),7) + 1;
69 dom := TO_NUMBER(TO_CHAR(temp_date+offset,'DD'));
70 nextdom := TO_NUMBER(TO_CHAR(temp_date+1+offset,'DD'));
71
72 -- handle 56 bits code
73 if (LENGTH(class_info) = 56) then
74 begin
75 moy := TO_NUMBER(TO_CHAR(temp_date + offset,'MM'));
76 weekno := TO_NUMBER(TO_CHAR(temp_date + offset,'W'));
77
78 if (SUBSTR(class_info,44 + moy,1) = '1') /* if month is specified */ then
79 begin
80 -- see if there is some matching schedule
81 -- either day of month is matched OR (its last day of month and week is current week
82 if ( ((SUBSTR(class_info,dom,1) = '1') or ((SUBSTR(class_info,32,1) = '1') and (nextdom = 1)))
83 or ( (SUBSTR(class_info,dow + 32,1) = '1') and (SUBSTR(class_info,weekno + 39,1) = '1')) ) then
84 found := 1;
85 end if;
86 end;
87 end if;
88 end;
89 else
90 -- handle 39 bits code as usual for backward compatibility
91 if (SUBSTR(class_info,dom,1) = '1') or /* if date matches */
92 ((SUBSTR(class_info,32,1) = '1') and (nextdom = 1)) or
93 (SUBSTR(class_info,dow + 32,1) = '1') then
94 found := 1;
95 end if;
96 end if;
97 end loop;
98
99 /* the bitfield must be all zeros...we don't have to take this abuse */
100 if found = 0 then return null;
101 else return greatest(req_sdate, temp_date + offset);
102 end if;
103
104 end calc_specific_startdate;
105
106
107 -- Name
108 -- parse_named_periodic_schedule
109 -- Purpose
110 -- Given an application name and a Periodic schedule name,
111 -- return the interval, interval unit, interval type, start_date and end date
112 -- for this schedule.
113 -- Values will be null if the schedule is not found or an error occurs.
114 --
115 procedure parse_named_periodic_schedule(p_application in varchar2,
116 p_class_name in varchar2,
117 p_repeat_interval out nocopy number,
118 p_repeat_interval_unit out nocopy varchar2,
119 p_repeat_interval_type out nocopy varchar2,
120 p_start_date out nocopy date,
121 p_repeat_end out nocopy date) is
122
123 p_rel_class_app_id number;
124 p_rel_class_id number;
125 begin
126 p_repeat_interval := null;
127 p_repeat_interval_unit := null;
128 p_repeat_interval_type := null;
129 p_repeat_end := null;
130 p_start_date := null;
131
132 select a.application_id, c.release_class_id
133 into p_rel_class_app_id, p_rel_class_id
134 from fnd_conc_release_classes c, fnd_application a
135 where upper(release_class_name) = upper(p_class_name)
136 and c.application_id = a.application_id
137 and a.application_short_name = upper(p_application);
138
139 parse_periodic_schedule(p_rel_class_app_id, p_rel_class_id,
140 p_repeat_interval,
141 p_repeat_interval_unit,
142 p_repeat_interval_type,
143 p_start_date,
144 p_repeat_end);
145
146
147 exception
148 when no_data_found then
149 return;
150
151 end parse_named_periodic_schedule;
152
153
154
155 -- Name
156 -- parse_periodic_schedule
157 -- Purpose
158 -- Given an application id and a Periodic schedule id,
159 -- return the interval, interval unit, interval type, start_date and end date
160 -- for this schedule.
161 -- Values will be null if the schedule is not found or an error occurs.
162 --
163 procedure parse_periodic_schedule(p_rel_class_app_id in number,
164 p_rel_class_id in number,
165 p_repeat_interval out nocopy number,
166 p_repeat_interval_unit out nocopy varchar2,
167 p_repeat_interval_type out nocopy varchar2,
168 p_start_date out nocopy date,
169 p_repeat_end out nocopy date) is
170
171 c1 number;
172 c2 number;
173 sch_type varchar2(1);
174 curr_info varchar2(64);
175 req_date date;
176 tmp_interval number;
177 begin
178 p_repeat_interval := null;
179 p_repeat_interval_unit := null;
180 p_repeat_interval_type := null;
181 p_repeat_end := null;
182 p_start_date := null;
183
184 select class_info, class_type, date1, date2
185 into curr_info, sch_type, req_date, p_repeat_end
186 from fnd_conc_release_classes
187 where application_id = p_rel_class_app_id
188 and release_class_id = p_rel_class_id;
189
190 if sch_type <> 'P' then
191 return;
192 end if;
193
194 c1 := instr(curr_info, ':', 1, 1);
195 c2 := instr(curr_info, ':', 1, 2);
196
197 -- interval is the first field
198 p_repeat_interval := to_number(substr(curr_info, 1, (c1-1)));
199
200 -- interval unit
201 select decode(substr(curr_info, c1+1, 1),
202 'M', 'MONTHS', 'D', 'DAYS', 'H', 'HOURS', 'N', 'MINUTES')
203 into p_repeat_interval_unit
204 from dual;
205
206 -- interval type
207 select decode(substr(curr_info, (c2+1), 1), 'C', 'END', 'START')
208 into p_repeat_interval_type
209 from dual;
210
211
212 -- now figure out the correct requested start date
213 select floor((sysdate - req_date) / DECODE(p_repeat_interval_unit,
214 'MINUTES', GREATEST(p_repeat_interval,1) / 1440,
215 'HOURS', GREATEST(p_repeat_interval,1/60) / 24,
216 'DAYS', GREATEST(p_repeat_interval,1/1440),
217 'MONTHS', GREATEST(p_repeat_interval,1) * 31,1))
218 into tmp_interval
219 from dual;
220
221 if (tmp_interval > 0) then
222 select req_date +
223 DECODE(p_repeat_interval_unit,
224 'MINUTES', tmp_interval * GREATEST(p_repeat_interval,1) / 1440,
225 'HOURS', tmp_interval * GREATEST(p_repeat_interval,1/60) / 24,
226 'DAYS', tmp_interval * GREATEST(p_repeat_interval,1/1440),
227 'MONTHS', ADD_MONTHS(req_date, tmp_interval * p_repeat_interval)-req_date,
228 1)
229 into req_date
230 from dual;
231 end if;
232
233 while (req_date < sysdate) loop
234 select req_date +
235 DECODE(p_repeat_interval_unit,
236 'MINUTES', GREATEST(p_repeat_interval,1) / 1440,
237 'HOURS', GREATEST(p_repeat_interval,1/60) / 24,
238 'DAYS', GREATEST(p_repeat_interval,1/1440),
239 'MONTHS', ADD_MONTHS(req_date,p_repeat_interval)-req_date,
240 1)
241 into req_date
242 from dual;
243 end loop;
244
245
246 p_start_date := req_date;
247
248 exception
249 when no_data_found then
250 null;
251
252 end parse_periodic_schedule;
253
254 -- Name
255 -- assign_specific_sch
256 -- Purpose
257 -- this function assigns specific schedule to a request
258 -- return true if successful
259 -- May return false if unsuccessful
260 --
261 function assign_specific_sch (req_id in number,
262 class_info in varchar2,
263 start_date in date,
264 end_date in date) return boolean is
265
266 relseqno number;
267 relclassname varchar2(20);
268 relclass_insert_error exception;
269 dual_no_rows exception;
270 dual_too_many_rows exception;
271
272 begin
273
274 begin
275 Select Fnd_Conc_Release_Classes_S.nextval
276 Into relseqno
277 From Sys.Dual;
278
279 exception
280 when no_data_found then
281 raise dual_no_rows;
282 when too_many_rows then
283 raise dual_too_many_rows;
284 when others then
285 raise;
286 end;
287
288 relclassname := 'RSRUN:0-' || TO_CHAR(relseqno);
289
290 Insert Into fnd_conc_release_classes (application_id, release_class_id, release_class_name, owner_req_id, enabled_flag,
291 creation_date, created_by, last_update_date, last_updated_by, last_update_login, updated_flag, date1, date2, class_type,
292 class_info)
293 Values (0 , relseqno, relclassname, req_id, 'Y', Sysdate, FND_GLOBAL.conc_login_id, Sysdate,
294 FND_GLOBAL.conc_login_id, FND_GLOBAL.conc_login_id, 'N', start_date, end_date, 'S', class_info);
295
296 if (sql%rowcount = 0) then
297 raise relclass_insert_error;
298 end if;
299
300 INSERT INTO Fnd_Conc_Release_Classes_TL (application_id, release_class_id, language, source_lang,
301 user_release_class_name,last_update_date, last_updated_by, last_update_login, creation_date, created_by)
302 Select 0, relseqno, L.LANGUAGE_CODE, userenv('LANG'), relclassname,
303 Sysdate, FND_GLOBAL.conc_login_id, FND_GLOBAL.conc_login_id, Sysdate, FND_GLOBAL.conc_login_id from FND_LANGUAGES L
304 where L.INSTALLED_FLAG in ('I', 'B');
305
306 if (sql%rowcount = 0) then
307 raise relclass_insert_error;
308 end if;
309
310 -- modify FND_CONCURRENT_REQUESTS to set the release class id for this request
311 update Fnd_Concurrent_Requests set release_class_id=relseqno, release_class_app_id=0 where request_id=req_id;
312
313 if (sql%rowcount = 0) then
314 raise relclass_insert_error;
315 end if;
316
317 return (true);
318
319 exception
320 when relclass_insert_error then
321 fnd_message.set_name ('FND', 'CONC-RelClass insert failed');
322 fnd_message.set_token ('APPLICATION', 0, FALSE);
323 fnd_message.set_token ('CLASS', relseqno, FALSE);
324 return (false);
325 when dual_no_rows then
326 fnd_message.set_name ('FND', 'No Rows in Dual');
327 return (false);
328 when dual_too_many_rows then
329 fnd_message.set_name ('FND', 'Too many rows in Dual');
330 return (false);
331 when others then
332 fnd_message.set_name ('FND', 'SQL-Generic error');
333 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
334 fnd_message.set_token ('REASON', sqlerrm, FALSE);
338 end assign_specific_sch;
335 fnd_message.set_token ('ROUTINE', 'assign_specific_sch: others', FALSE);
336 return (false);
337
339
340
341 end FND_CONC_RELEASE_CLASS_UTILS;
[Home] [Help]