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