1 PACKAGE BODY gl_scheduler_pkg AS
2 /* $Header: gluschib.pls 120.2 2005/05/05 01:43:17 kvora ship $ */
3
4 FUNCTION create_schedule( sched_name IN VARCHAR2,
5 calendar_name IN VARCHAR2,
6 period_type_name IN VARCHAR2,
7 run_day IN NUMBER,
8 run_time IN VARCHAR2,
9 create_flag IN BOOLEAN )
10 RETURN NUMBER IS
11 result_code NUMBER;
12 period_name VARCHAR2(15);
13 gl_period_type VARCHAR2(15);
14 start_date DATE;
15 end_date DATE;
16 aol_start_date DATE;
17 aol_end_date DATE;
18 temp VARCHAR2(100);
19 conc_period VARCHAR2(20);
20 conc_disj VARCHAR2(20);
21 curr_seq_val VARCHAR2(15);
22 table_name VARCHAR2(30);
23 msgbuf VARCHAR2(2000);
24 lang_code VARCHAR2(4);
25
26 CURSOR period_cursor ( p_period_set_name IN VARCHAR2,
27 p_period_type IN VARCHAR2 ) IS
28 SELECT PER.period_name,
29 PER.start_date,
30 PER.end_date
31 FROM gl_periods PER
32 WHERE PER.period_set_name = p_period_set_name
33 AND PER.period_type = p_period_type
34 AND TRUNC( PER.end_date ) >= TRUNC( SYSDATE )
35 AND PER.adjustment_period_flag = 'N';
36
37 BEGIN
38 -- initialize period type for further use in the routine
39 gl_period_type := period_type_name;
40
41 -- initialize language code for the user environment
42 SELECT userenv( 'LANG' )
43 INTO lang_code
44 FROM sys.dual;
45
46 -- initialize message buffer to hold the description which
47 -- will be used while registering periods, disjunctions and classes.
48 msgbuf := fnd_message.get_string( 'SQLGL', 'GL_SCH_DESC_MESSAGE' );
49 msgbuf := SUBSTRB( msgbuf, 1, 80 );
50
51 -- if create_flag is TRUE, then
52 -- register the concurrent release class, if it doesn't exist.
53 -- if it exists, exit with error.
54 -- if create_flag is FALSE, then don't register the class.
55
56 IF create_flag = TRUE THEN
57 IF FNDCP_SCH.Class_Exists( 'SQLGL', sched_name ) = TRUE
58 THEN
59 RETURN (-1);
60 -- -1 = a schedule by this name already exists
61 ELSE
62 FNDCP_SCH.Class( 'SQLGL', sched_name, sched_name,
63 msgbuf, lang_code );
64 FNDCP_SCH.Set_Class_Resub( 'SQLGL', sched_name, 1, 'SMART',
65 'START');
66 -- Set_Class_Resub sets the resubmission parameters for the
67 -- schedule. When it is called with the 'SMART' mode, the
68 -- scheduler detects the start time of the next period and
69 -- schedules the spawned request accordingly.
70
71 END IF;
72 END IF;
73
74 -- open cursor;
75 OPEN period_cursor( calendar_name, gl_period_type );
76
77 LOOP
78
79 -- fetch rows;
80 FETCH period_cursor INTO period_name, start_date, end_date;
81 EXIT WHEN period_cursor%NOTFOUND;
82
83 -- determine start date, time and stop date, time
84 aol_start_date := start_date + ( run_day - 1 );
85 IF aol_start_date > end_date THEN
86 aol_start_date := end_date;
87 END IF;
88
89 temp := CONCAT( TO_CHAR( aol_start_date, 'YYYY/MM/DD' ), run_time);
90 aol_start_date := TO_DATE( temp, 'YYYY/MM/DD HH24:MI:SS' );
91
92 -- add 12 hours to the start date to determine the end time
93 aol_end_date := aol_start_date + 0.5;
94
95 -- select unique number from sequence to name the new period and
96 -- the disjunction associated with it
97 SELECT TO_CHAR( gl_concurrent_schedules_s.NEXTVAL )
98 INTO curr_seq_val
99 FROM SYS.dual;
100
101 -- register new concurrent release period
102 conc_period := CONCAT( curr_seq_val, '_P' );
103 IF FNDCP_SCH.Period_Exists( 'SQLGL', conc_period ) = TRUE THEN
104 CLOSE period_cursor;
105 RETURN( -2 );
106 -- -2 : period already exists
107 ELSE
108 FNDCP_SCH.Period( 'SQLGL', conc_period, conc_period,
109 msgbuf, 'M',
110 999,
111 aol_start_date,
112 aol_end_date,
113 lang_code );
114 END IF;
115
116 -- register new concurrent release disjunction
117 conc_disj := CONCAT( curr_seq_val, '_D' );
118
119 IF FNDCP_SCH.Disjunction_Exists( 'SQLGL', conc_disj ) = TRUE THEN
120 CLOSE period_cursor;
121 RETURN( -3 );
122 -- -3 : disjunction already exists
123 ELSE
124 FNDCP_SCH.Disjunction( 'SQLGL', conc_disj, conc_disj,
125 msgbuf,
126 lang_code );
127 END IF;
128
129 -- add newly created period to newly created disjunction
130 IF FNDCP_SCH.Disj_Member_Exists( 'SQLGL', conc_disj,
131 'SQLGL', conc_period,
132 'P' ) = TRUE THEN
133 CLOSE period_cursor;
134 RETURN( -4 );
135 -- -4 : period already exists in disjunction
136 ELSE
137 FNDCP_SCH.Disj_Member_P( 'SQLGL', conc_disj,
138 'SQLGL', conc_period,
139 'N' );
140 END IF;
141
142 -- add newly created disjunction to the class
143 IF FNDCP_SCH.Class_Member_Exists( 'SQLGL', sched_name,
144 'SQLGL', conc_disj ) = TRUE
145 THEN
146 CLOSE period_cursor;
147 RETURN( -5 );
148 -- -5 : disjunction already exists in class
149 ELSE
150 FNDCP_SCH.Class_Member( 'SQLGL', sched_name,
151 'SQLGL', conc_disj );
152 END IF;
153
154 END LOOP;
155 CLOSE period_cursor;
156 FNDCP_SCH.Commit_Changes;
157 RETURN( 0 );
158
159 EXCEPTION
160
161 WHEN NO_DATA_FOUND THEN
162 RETURN ( -6 );
163 -- -6 : period type doesn't exist
164
165 END create_schedule;
166
167
168 FUNCTION cleanup_schedule( sched_name IN VARCHAR2 )
169 RETURN NUMBER IS
170
171 rel_class_id NUMBER;
172 disj_id NUMBER;
173 disj_name VARCHAR2(20);
174 per_name VARCHAR2(20);
175 table_name VARCHAR2(30);
176 return_code NUMBER;
177 my_rowid1 ROWID;
178 my_rowid2 ROWID;
179 NO_UPDATE EXCEPTION;
180
181 -- select members of the class
182 CURSOR disj_cursor( p_class_id IN NUMBER ) IS
183 SELECT DISJ.disjunction_id, DISJ.disjunction_name, DISJ.rowid
184 FROM fnd_conc_release_disjs DISJ,
185 fnd_conc_rel_conj_members MEMB
186 WHERE DISJ.application_id = 101
187 AND MEMB.class_application_id = 101
188 AND MEMB.release_class_id = p_class_id
189 AND MEMB.disjunction_application_id = 101
190 AND MEMB.disjunction_id = DISJ.disjunction_id;
191
192
193 -- select members of the disjunction
194 CURSOR period_cursor( p_disj_id IN NUMBER ) IS
195 SELECT PER.concurrent_period_name, PER.rowid
196 FROM fnd_conc_release_periods PER,
197 fnd_conc_rel_disj_members MEMB
198 WHERE PER.application_id = 101
199 AND MEMB.disjunction_application_id = 101
200 AND MEMB.disjunction_id = p_disj_id
201 AND MEMB.period_or_state_flag = 'P'
202 AND MEMB.period_application_id = 101
203 AND MEMB.period_id = PER.concurrent_period_id;
204
205 BEGIN
206 -- select class_id given class name
207
208 return_code := -1;
209 -- -1 : schedule does not exist
210 -- set return_code to -1 for no_data_found exception
211 -- if the select statement succeeds, reset return_code to 0
212 -- to continue normal processing.
213 SELECT CLS.release_class_id
214 INTO rel_class_id
215 FROM fnd_conc_release_classes CLS
216 WHERE CLS.application_id = 101
217 AND CLS.release_class_name = sched_name;
218
219 return_code := 0;
220
221 -- open cursor to retrieve members of the class
222 OPEN disj_cursor( rel_class_id );
223
224 LOOP
225 FETCH disj_cursor INTO disj_id, disj_name, my_rowid1;
226 EXIT WHEN disj_cursor%NOTFOUND;
227
228 -- open cursor to retrieve periods of the disjunction
229 OPEN period_cursor( disj_id );
230
231 LOOP
232 FETCH period_cursor INTO per_name, my_rowid2;
233 EXIT WHEN period_cursor%NOTFOUND;
234
235 -- remove member period from the disjunction
236 FNDCP_SCH.Disj_Dismember( 'SQLGL', disj_name,
237 'SQLGL', per_name, 'P' );
238
239 -- disable member period of the disjunction
240 -- UPDATE fnd_conc_release_periods PER
241 -- SET PER.enabled_flag = 'N'
242 -- WHERE PER.rowid = my_rowid2;
243
244 -- IF ( SQL%NOTFOUND ) THEN
245 -- -2 : Update failed on fnd_conc_release_periods
246 -- return_code := -2;
247 -- RAISE NO_UPDATE;
248 -- END IF;
249
250 END LOOP;
251 CLOSE period_cursor;
252
253 -- remove member disjunction from the class
254 FNDCP_SCH.Class_Dismember( 'SQLGL', sched_name,
255 'SQLGL', disj_name );
256
257 -- disable member disjunction of the class
258 -- UPDATE fnd_conc_release_disjs DIS
259 -- SET DIS.enabled_flag = 'N'
260 -- WHERE DIS.rowid = my_rowid1;
261
262 -- IF ( SQL%NOTFOUND ) THEN
263 -- -3 : Update failed on fnd_conc_release_disjs
264 -- return_code := -3;
265 -- RAISE NO_UPDATE;
266 -- END IF;
267
268 END LOOP;
269 CLOSE disj_cursor;
270 FNDCP_SCH.Commit_Changes;
271 RETURN ( return_code );
272
273 EXCEPTION
274 WHEN NO_DATA_FOUND THEN
275 RETURN ( return_code );
276
277 WHEN NO_UPDATE THEN
278 RETURN ( return_code );
279
280 END cleanup_schedule;
281
282 FUNCTION update_schedules ( x_period_set_name IN VARCHAR2 )
283 RETURN NUMBER IS
284 l_sched_name VARCHAR2(20);
285 l_per_type VARCHAR2(15);
286 l_run_day NUMBER;
287 l_run_time VARCHAR2(15);
288 ret_code NUMBER;
289
290 -- this cursor selects all schedules associated with the period_set_name
291 -- p_period_set_name.
292 CURSOR schedule_cursor ( p_period_set_name IN VARCHAR2) IS
293 SELECT SCH.schedule_name,
294 SCH.period_type,
295 SCH.run_day,
296 TO_CHAR( SCH.run_time, 'HH24:MI:SS' )
297 FROM gl_concurrent_schedules SCH
298 WHERE SCH.period_set_name = p_period_set_name;
299
300 BEGIN
301
302 OPEN schedule_cursor( x_period_set_name );
303 LOOP
304 FETCH schedule_cursor INTO l_sched_name,
305 l_per_type,
306 l_run_day,
307 l_run_time;
308 EXIT WHEN schedule_cursor%NOTFOUND;
309
310 -- call cleanup_schedule( ) to prepare the schedule for update
311 ret_code := gl_scheduler_pkg.cleanup_schedule( l_sched_name );
312 IF ( ret_code <> 0 ) THEN
313 fnd_message.set_name( 'SQLGL', 'GL_SCH_INT_ERROR' );
314 UPDATE gl_concurrent_schedules SCH
315 SET SCH.enabled_flag = 'N'
316 WHERE SCH.schedule_name = l_sched_name;
317
318 -- ** Call FND API to disable schedule here ** --
319 FNDCP_SCH.Class_Disable( 'SQLGL', l_sched_name );
320 FNDCP_SCH.Commit_Changes;
321
322 CLOSE schedule_cursor;
323 RETURN( -1 );
324 END IF;
325
326 -- call create_schedule( ) to update the schedule based upon the
327 -- new calendar.
328 ret_code := gl_scheduler_pkg.create_schedule( l_sched_name,
329 x_period_set_name,
330 l_per_type,
331 l_run_day,
332 l_run_time,
333 FALSE );
334 IF ( ret_code <> 0 ) THEN
335 fnd_message.set_name( 'SQLGL', 'GL_SCH_INT_ERROR' );
336 UPDATE gl_concurrent_schedules SCH
337 SET SCH.enabled_flag = 'N'
338 WHERE SCH.schedule_name = l_sched_name;
339
340 -- ** Call FND API to disable schedule here ** --
341 FNDCP_SCH.Class_Disable( 'SQLGL', l_sched_name );
342 FNDCP_SCH.Commit_Changes;
343
344 CLOSE schedule_cursor;
345 RETURN( -2 );
346 END IF;
347 END LOOP;
348 CLOSE schedule_cursor;
349 FNDCP_SCH.Commit_Changes;
350 RETURN( 0 );
351 END update_schedules;
352
353 END gl_scheduler_pkg;
[Home] [Help]