[Home] [Help]
PACKAGE BODY: APPS.CSFW_SETUP_PKG
Source
1 PACKAGE BODY CSFW_SETUP_PKG AS
2 /* $Header: csfwsetupb.pls 120.2 2011/01/05 11:16:20 shadas noship $ */
3
4 PROCEDURE insert_phonetics_row
5 (
6 p_phonetics_id IN VARCHAR2 ,
7 p_language IN VARCHAR2 ,
8 p_phonetic IN VARCHAR2 ,
9 p_code IN VARCHAR2 ,
10 p_description IN VARCHAR2 ,
11 p_enabled_flag IN VARCHAR2 ,
12 p_start_date_active IN VARCHAR2 ,
13 p_end_date_active IN VARCHAR2 ,
14 p_created_by IN VARCHAR2 ,
15 p_creation_date IN VARCHAR2 ,
16 p_last_updated_by IN VARCHAR2 ,
17 p_last_update_login IN VARCHAR2 ,
18 p_last_update_date IN VARCHAR2 )
19 IS
20 BEGIN
21 /*
22 IF p_created_by IS NULL THEN
23 p_created_by := fnd_global.user_id;
24 END IF;
25 IF p_last_updated_by IS NULL THEN
26 p_last_updated_by := fnd_global.user_id;
27 END IF;
28 IF p_last_update_login IS NULL THEN
29 p_last_update_login := fnd_global.login_id;
30 END IF;
31 IF p_creation_date IS NULL THEN
32 p_creation_date := sysdate;
33 END IF;
34 IF p_last_update_date IS NULL THEN
35 p_last_update_date := sysdate;
36 END IF;
37 */
38 INSERT
39 INTO csf_phonetic_values
40 (
41 phonetics_id,
42 language,
43 phonetic,
44 code,
45 description,
46 enabled_flag,
47 start_date_active,
48 end_date_active,
49 created_by,
50 creation_date,
51 last_updated_by,
52 last_update_login,
53 last_update_date
54 )
55 VALUES
56 (
57 p_phonetics_id,
58 p_language,
59 p_phonetic,
60 p_code,
61 p_description,
62 p_enabled_flag,
63 decode(p_start_date_active,NULL,NULL,to_date(p_start_date_active, 'YYYY/MM/DD')),
64 decode(p_end_date_active,NULL,NULL,to_date(p_end_date_active, 'YYYY/MM/DD')),
65 p_created_by,
66 to_date(p_creation_date, 'YYYY/MM/DD'),
67 p_last_updated_by,
68 p_last_update_login,
69 to_date(p_last_update_date, 'YYYY/MM/DD')
70 );
71 END insert_phonetics_row;
72
73 PROCEDURE update_phonetics_row
74 (
75 p_phonetics_id IN VARCHAR2 ,
76 p_language IN VARCHAR2 ,
77 p_phonetic IN VARCHAR2 ,
78 p_code IN VARCHAR2 ,
79 p_description IN VARCHAR2 ,
80 p_enabled_flag IN VARCHAR2 ,
81 p_start_date_active IN VARCHAR2 ,
82 p_end_date_active IN VARCHAR2 ,
83 p_created_by IN VARCHAR2 ,
84 p_creation_date IN VARCHAR2 ,
85 p_last_updated_by IN VARCHAR2 ,
86 p_last_update_login IN VARCHAR2 ,
87 p_last_update_date IN VARCHAR2
88 )
89 IS
90 l_phonetics_id NUMBER;
91 BEGIN
92 l_phonetics_id := to_number(p_phonetics_id);
93 /*
94 if p_last_updated_by is null then
95 p_last_updated_by := fnd_global.user_id;
96 end if;
97 if p_last_update_login is null then
98 p_last_update_login := fnd_global.login_id;
99 end if;
100 if p_last_update_date is null then
101 p_last_update_date := sysdate;
102 end if;
103 */
104 UPDATE csf_phonetic_values
105 SET language = p_language,
106 phonetic = p_phonetic,
107 code = p_code,
108 description = p_description,
109 enabled_flag = p_enabled_flag,
110 start_date_active = decode(p_start_date_active,NULL,NULL,to_date(p_start_date_active, 'YYYY/MM/DD')),
111 end_date_active = decode(p_end_date_active,NULL,NULL,to_date(p_end_date_active, 'YYYY/MM/DD')),
112 created_by = p_created_by,
113 creation_date = to_date(p_creation_date, 'YYYY/MM/DD'),
114 last_updated_by = p_last_updated_by,
115 last_update_login = p_last_update_login,
116 last_update_date = to_date(p_last_update_date, 'YYYY/MM/DD')
117 WHERE phonetics_id = l_phonetics_id;
118 IF sql%notfound THEN
119 raise NO_DATA_FOUND;
120 END IF;
121 END update_phonetics_row;
122
123 PROCEDURE load_phonetics_row
124 (
125 p_phonetics_id IN VARCHAR2 ,
126 p_language IN VARCHAR2 ,
127 p_phonetic IN VARCHAR2 ,
128 p_code IN VARCHAR2 ,
129 p_description IN VARCHAR2 ,
130 p_enabled_flag IN VARCHAR2 ,
131 p_start_date_active IN VARCHAR2 ,
132 p_end_date_active IN VARCHAR2 ,
133 p_created_by IN VARCHAR2 ,
134 p_creation_date IN VARCHAR2 ,
135 p_last_updated_by IN VARCHAR2 ,
136 p_last_update_login IN VARCHAR2 ,
137 p_last_update_date IN VARCHAR2 )
138 IS
139 BEGIN
140 update_phonetics_row (
141 p_phonetics_id => p_phonetics_id,
142 p_language => p_language,
143 p_phonetic => p_phonetic,
144 p_code => p_code,
145 p_description => p_description,
146 p_enabled_flag => p_enabled_flag,
147 p_start_date_active => p_start_date_active,
148 p_end_date_active => p_end_date_active,
149 p_created_by => p_created_by,
150 p_creation_date => p_creation_date,
151 p_last_updated_by => p_last_updated_by,
152 p_last_update_login => p_last_update_login,
153 p_last_update_date => p_last_update_date
154 );
155 EXCEPTION
156 WHEN NO_DATA_FOUND THEN
157 insert_phonetics_row (
158 p_phonetics_id => p_phonetics_id,
159 p_language => p_language,
160 p_phonetic => p_phonetic,
161 p_code => p_code,
162 p_description => p_description,
163 p_enabled_flag => p_enabled_flag,
164 p_start_date_active => p_start_date_active,
165 p_end_date_active => p_end_date_active,
166 p_created_by => p_created_by,
167 p_creation_date => p_creation_date,
168 p_last_updated_by => p_last_updated_by,
169 p_last_update_login => p_last_update_login,
170 p_last_update_date => p_last_update_date
171 );
172 END load_phonetics_row;
173
174 PROCEDURE purge_server_session (
175 errbuf OUT NOCOPY VARCHAR2,
176 retcode OUT NOCOPY VARCHAR2,
177 p_caller IN VARCHAR2 := 'CSFWPRGE',
178 p_end_date IN VARCHAR2 := NULL
179 ) IS
180
181 l_log_module varchar2(50) := 'csf.plsql.CSFW_SETUP_PKG.purge_server_session';
182 l_deleted_records number;
183 l_default_retain_days number := 1;
184 l_end_date date;
185
186 cursor get_old_seq_name is
187 SELECT sequence_name
188 FROM all_sequences
189 WHERE sequence_name LIKE 'CSFW_MSESSION_URLID_S%'
190 AND sequence_owner = USER;
191
192 l_seq_ddl_str varchar2(100);
193 BEGIN
194
195 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
196 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
197 'Begin...');
198 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
199 'p_caller = ' || p_caller);
200 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
201 'p_end_date = ' || p_end_date);
202 end if;
203
204 savepoint csfw_purge;
205
206 if p_end_date is NULL then
207 l_end_date := trunc(sysdate - l_default_retain_days);
208 else
209 l_end_date := trunc(fnd_date.canonical_to_date(p_end_date));
210 end if;
211
212 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
213 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
214 'l_end_date = ' || l_end_date);
215 end if;
216
217 -- block for CSFW_MSESSION_COOKIE
218 begin
219 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
220 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
221 'Start deleting CSFW_MSESSION_COOKIE ...');
222 end if;
223
224 DELETE FROM CSFW_MSESSION_COOKIE
225 WHERE session_id IN
226 (SELECT session_id
227 FROM CSFW_MSESSION
228 WHERE last_access_date <= l_end_date
229 ) RETURNING count(*) INTO l_deleted_records;
230
231 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
232 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
233 'Deleted records for CSFW_MSESSION_COOKIE = ' || l_deleted_records);
234 end if;
235
236 exception
237 when others then
238 rollback to csfw_purge;
239 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
240 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
241 'Exception occured while deleting CSFW_MSESSION_COOKIE sqlerrm = ' || sqlerrm);
242 end if;
243 fnd_message.set_name('CSF', 'CSF_CP_DONE_ERROR');
244 errbuf := fnd_message.get;
245 retcode := 2;
246 return;
247 end;
248 -- end block for CSFW_MSESSION_COOKIE
249
250 -- block for CSFW_MSESSION_URL
251 begin
252 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
253 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
254 'Start deleting CSFW_MSESSION_URL ...');
255 end if;
256
257 DELETE FROM CSFW_MSESSION_URL
258 WHERE session_id IN
259 (SELECT session_id
260 FROM CSFW_MSESSION
261 WHERE last_access_date <= l_end_date
262 ) RETURNING count(*) INTO l_deleted_records;
263
264 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
265 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
266 'Deleted records for CSFW_MSESSION_URL = ' || l_deleted_records);
267 end if;
268 exception
269 when others then
270 rollback to csfw_purge;
271 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
272 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
273 'Exception occured while deleting CSFW_MSESSION_URL sqlerrm = ' || sqlerrm);
274 end if;
275 fnd_message.set_name('CSF', 'CSF_CP_DONE_ERROR');
276 errbuf := fnd_message.get;
277 retcode := 2;
278 return;
279 end;
280 -- end block for CSFW_MSESSION_URL
281
282 -- block for CSFW_MSESSION
283 begin
284 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
285 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
286 'Start deleting CSFW_MSESSION ...');
287 end if;
288
289 DELETE FROM CSFW_MSESSION
290 WHERE last_access_date <= l_end_date
291 RETURNING count(*) INTO l_deleted_records;
292
293 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
294 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
295 'Deleted records for CSFW_MSESSION = ' || l_deleted_records);
296 end if;
297 exception
298 when others then
299 rollback to csfw_purge;
300 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
301 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
302 'Exception occured while deleting CSFW_MSESSION sqlerrm = ' || sqlerrm);
303 end if;
304 fnd_message.set_name('CSF', 'CSF_CP_DONE_ERROR');
305 errbuf := fnd_message.get;
306 retcode := 2;
307 return;
308 end;
309 -- end block for CSFW_MSESSION
310
311 -- now we can commit
312 commit;
313
314 -- need to clear old CSFW_MSESSION_URLID_S% sequences as well if it is there
315 begin
316 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
317 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
318 'Start clearing old CSFW_MSESSION_URLID_S% sequences ...');
319 end if;
320
321 for l_old_seq_name in get_old_seq_name
322 loop
323
324 l_seq_ddl_str := 'DROP sequence ' || l_old_seq_name.sequence_name;
325 EXECUTE IMMEDIATE l_seq_ddl_str;
326 end loop;
327 exception
328 when others then
329 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
330 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
331 'Exception occured while deleting CSFW_MSESSION_URLID_S% sequences sqlerrm = ' || sqlerrm);
332 end if;
333 end;
334
335 -- now we can commit
336 commit;
337
338 fnd_message.set_name('CSF', 'CSF_CP_DONE_SUCCESS');
339 errbuf := fnd_message.get;
340 retcode := 0;
341
342 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
343 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
344 'End of purge_server_session_java...');
345 end if;
346
347 END purge_server_session;
348
349 END CSFW_SETUP_PKG;
350