[Home] [Help]
PACKAGE BODY: APPS.XLA_EVENT_SOURCES_F_PKG
Source
1 PACKAGE BODY xla_event_sources_f_pkg AS
2 /* $Header: xlatbssa.pkb 120.12 2005/04/28 18:45:44 masada ship $ */
3 /*======================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_event_sources |
10 | |
11 | DESCRIPTION |
12 | Forms PL/SQL Wrapper for xla_event_sources |
13 | |
14 | HISTORY |
15 | 05/22/01 Dimple Shah Created |
16 | 05/20/04 W.Shen change for accounting attribute |
17 | enhancement project. reflect the change|
18 | to the xla_event_sources table |
19 | |
20 +======================================================================*/
21
22 --=============================================================================
23 -- *********** Local Trace Routine **********
24 --=============================================================================
25 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
26 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
27 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
28 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
29 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
30 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
31
32 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
33 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_event_sources_f_pkg';
34
35 g_debug_flag VARCHAR2(1) :=
36 NVL(fnd_profile.value('XLA_DEBUG_TRACE'),'N');
37
38 g_log_level NUMBER;
39 g_log_enabled BOOLEAN;
40
41 PROCEDURE trace
42 (p_msg IN VARCHAR2
43 ,p_module IN VARCHAR2
44 ,p_level IN NUMBER) IS
45 BEGIN
46 ----------------------------------------------------------------------------
47 -- Following is for FND log.
48 ----------------------------------------------------------------------------
49 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
50 fnd_log.message(p_level, p_module);
51 ELSIF p_level >= g_log_level THEN
52 fnd_log.string(p_level, p_module, p_msg);
53 END IF;
54
55 EXCEPTION
56 WHEN xla_exceptions_pkg.application_exception THEN
57 RAISE;
58
59 WHEN OTHERS THEN
60 xla_exceptions_pkg.raise_message
61 (p_location => 'xla_event_sources_f_pkg.trace');
62 END trace;
63
64
65
66 /*======================================================================+
67 | |
68 | Procedure insert_row |
69 | |
70 +======================================================================*/
71 PROCEDURE insert_row
72 (x_rowid IN OUT NOCOPY VARCHAR2
73 ,x_application_id IN NUMBER
74 ,x_entity_code IN VARCHAR2
75 ,x_event_class_code IN VARCHAR2
76 ,x_source_application_id IN NUMBER
77 ,x_source_type_code IN VARCHAR2
78 ,x_source_code IN VARCHAR2
79 ,x_level_code IN VARCHAR2
80 ,x_active_flag IN VARCHAR2
81 ,x_creation_date IN DATE
82 ,x_created_by IN NUMBER
83 ,x_last_update_date IN DATE
84 ,x_last_updated_by IN NUMBER
85 ,x_last_update_login IN NUMBER)
86
87 IS
88
89 CURSOR c IS
90 SELECT rowid
91 FROM xla_event_sources
92 WHERE application_id = x_application_id
93 AND entity_code = x_entity_code
94 AND event_class_code = x_event_class_code
95 AND source_application_id = x_source_application_id
96 AND source_type_code = x_source_type_code
97 AND source_code = x_source_code
98 ;
99
100 l_log_module VARCHAR2(240);
101 BEGIN
102
103 IF g_log_enabled THEN
104 l_log_module := C_DEFAULT_MODULE||'.insert_row';
105 END IF;
106
107 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
108 trace(p_msg => 'BEGIN of procedure insert_row',
109 p_module => l_log_module,
110 p_level => C_LEVEL_PROCEDURE);
111 END IF;
112
113
114 INSERT INTO xla_event_sources
115 (creation_date
116 ,created_by
117 ,application_id
118 ,entity_code
119 ,event_class_code
120 ,source_application_id
121 ,source_type_code
122 ,source_code
123 ,level_code
124 ,active_flag
125 ,last_update_date
126 ,last_updated_by
127 ,last_update_login)
128 VALUES
129 (x_creation_date
130 ,x_created_by
131 ,x_application_id
132 ,x_entity_code
133 ,x_event_class_code
134 ,x_source_application_id
135 ,x_source_type_code
136 ,x_source_code
137 ,x_level_code
138 ,x_active_flag
139 ,x_last_update_date
140 ,x_last_updated_by
141 ,x_last_update_login)
142 ;
143
144 OPEN c;
145 FETCH c INTO x_rowid;
146
147 IF (c%NOTFOUND) THEN
148 CLOSE c;
149 RAISE NO_DATA_FOUND;
150 END IF;
151 CLOSE c;
152
153 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
154 trace(p_msg => 'END of procedure insert_row',
155 p_module => l_log_module,
156 p_level => C_LEVEL_PROCEDURE);
157 END IF;
158
159 END insert_row;
160
161 /*======================================================================+
162 | |
163 | Procedure lock_row |
164 | |
165 +======================================================================*/
166 PROCEDURE lock_row
167 (x_rowid IN OUT NOCOPY VARCHAR2
168 ,x_application_id IN NUMBER
169 ,x_entity_code IN VARCHAR2
170 ,x_event_class_code IN VARCHAR2
171 ,x_source_application_id IN NUMBER
172 ,x_source_type_code IN VARCHAR2
173 ,x_source_code IN VARCHAR2
174 ,x_level_code IN VARCHAR2
175 ,x_active_flag IN VARCHAR2)
176
177 IS
178
179 CURSOR c IS
180 SELECT application_id
181 ,entity_code
182 ,event_class_code
183 ,source_application_id
184 ,source_type_code
185 ,source_code
186 ,level_code
187 ,active_flag
188 FROM xla_event_sources
189 WHERE application_id = x_application_id
190 AND entity_code = x_entity_code
191 AND event_class_code = x_event_class_code
192 AND source_application_id = x_source_application_id
193 AND source_type_code = x_source_type_code
194 AND source_code = x_source_code
195 FOR UPDATE OF application_id NOWAIT;
196
197 recinfo c%ROWTYPE;
198
199 l_log_module VARCHAR2(240);
200 BEGIN
201
202 IF g_log_enabled THEN
203 l_log_module := C_DEFAULT_MODULE||'.lock_row';
204 END IF;
205
206 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
207 trace(p_msg => 'BEGIN of procedure lock_row',
208 p_module => l_log_module,
209 p_level => C_LEVEL_PROCEDURE);
210 END IF;
211
212 OPEN c;
213 FETCH c INTO recinfo;
214
215 IF (c%NOTFOUND) THEN
216 CLOSE c;
217 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
218 app_exception.raise_exception;
219 END IF;
220 CLOSE c;
221
222 IF ( (recinfo.application_id = x_application_id)
223 AND (recinfo.entity_code = x_entity_code)
224 AND (recinfo.event_class_code = x_event_class_code)
225 AND (recinfo.source_application_id = x_source_application_id)
226 AND (recinfo.source_code = x_source_code)
227 AND (recinfo.source_type_code = x_source_type_code)
228 AND (recinfo.level_code = x_level_code)
229 AND (recinfo.active_flag = x_active_flag)
230 ) THEN
231 null;
232 ELSE
233 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
234 app_exception.raise_exception;
235 END IF;
236
237 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
238 trace(p_msg => 'END of procedure lock_row',
239 p_module => l_log_module,
240 p_level => C_LEVEL_PROCEDURE);
241 END IF;
242
243 RETURN;
244
245 END lock_row;
246
247 /*======================================================================+
248 | |
249 | Procedure update_row |
250 | |
251 +======================================================================*/
252 PROCEDURE update_row
253 (x_rowid IN OUT NOCOPY VARCHAR2
254 ,x_application_id IN NUMBER
255 ,x_entity_code IN VARCHAR2
256 ,x_event_class_code IN VARCHAR2
257 ,x_source_application_id IN NUMBER
258 ,x_source_type_code IN VARCHAR2
259 ,x_source_code IN VARCHAR2
260 ,x_level_code IN VARCHAR2
261 ,x_active_flag IN VARCHAR2
262 ,x_last_update_date IN DATE
263 ,x_last_updated_by IN NUMBER
264 ,x_last_update_login IN NUMBER)
265 IS
266
267 l_log_module VARCHAR2(240);
268 BEGIN
269
270 IF g_log_enabled THEN
271 l_log_module := C_DEFAULT_MODULE||'.update_row';
272 END IF;
273
274 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
275 trace(p_msg => 'BEGIN of procedure update_row',
276 p_module => l_log_module,
277 p_level => C_LEVEL_PROCEDURE);
278 END IF;
279
280 UPDATE xla_event_sources
281 SET
282 last_update_date = x_last_update_date
283 ,level_code = x_level_code
284 ,active_flag = x_active_flag
285 ,last_updated_by = x_last_updated_by
286 ,last_update_login = x_last_update_login
287 WHERE application_id = X_application_id
288 AND entity_code = x_entity_code
289 AND event_class_code = x_event_class_code
290 AND source_application_id = x_source_application_id
291 AND source_type_code = X_source_type_code
292 AND source_code = X_source_code;
293
294 IF (SQL%NOTFOUND) THEN
295 RAISE NO_DATA_FOUND;
296 END IF;
297
298 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
299 trace(p_msg => 'END of procedure update_row',
300 p_module => l_log_module,
301 p_level => C_LEVEL_PROCEDURE);
302 END IF;
303
304 END update_row;
305
306 /*======================================================================+
307 | |
308 | Procedure delete_row |
309 | |
310 +======================================================================*/
311 PROCEDURE delete_row
312 (x_application_id IN NUMBER
313 ,x_entity_code IN VARCHAR2
314 ,x_event_class_code IN VARCHAR2
315 ,x_source_application_id IN NUMBER
316 ,x_source_type_code IN VARCHAR2
317 ,x_source_code IN VARCHAR2)
318
319 IS
320
321 l_log_module VARCHAR2(240);
322 BEGIN
323
324 IF g_log_enabled THEN
325 l_log_module := C_DEFAULT_MODULE||'.delete_row';
326 END IF;
327
328 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
329 trace(p_msg => 'BEGIN of procedure delete_row',
330 p_module => l_log_module,
331 p_level => C_LEVEL_PROCEDURE);
332 END IF;
333
334 DELETE FROM xla_event_sources
335 WHERE application_id = x_application_id
336 AND entity_code = x_entity_code
340 AND source_code = x_source_code;
337 AND event_class_code = x_event_class_code
338 AND source_application_id = x_source_application_id
339 AND source_type_code = x_source_type_code
344 RAISE NO_DATA_FOUND;
341
342
343 IF (SQL%NOTFOUND) THEN
345 END IF;
346
347 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
348 trace(p_msg => 'END of procedure delete_row',
349 p_module => l_log_module,
350 p_level => C_LEVEL_PROCEDURE);
351 END IF;
352
353 END delete_row;
354
355 --=============================================================================
356 --
357 -- Name: load_row
358 -- Description: To be used by FNDLOAD to upload a row to the table
359 --
360 --=============================================================================
361 PROCEDURE load_row
362 (p_application_short_name IN VARCHAR2
363 ,p_entity_code IN VARCHAR2
364 ,p_event_class_code IN VARCHAR2
365 ,p_source_app_short_name IN VARCHAR2
366 ,p_source_type_code IN VARCHAR2
367 ,p_source_code IN VARCHAR2
368 ,p_active_flag IN VARCHAR2
369 ,p_level_code IN VARCHAR2
370 ,p_owner IN VARCHAR2
371 ,p_last_update_date IN VARCHAR2)
372 IS
373 CURSOR c_app_id(p_app_short_name VARCHAR2) IS
374 SELECT application_id
375 FROM fnd_application
376 WHERE application_short_name = p_app_short_name;
377
378 l_application_id INTEGER;
379 l_source_app_id INTEGER;
380 l_rowid ROWID;
381 l_exist VARCHAR2(1);
382 f_luby NUMBER; -- entity owner in file
383 f_ludate DATE; -- entity update date in file
384 db_luby NUMBER; -- entity owner in db
385 db_ludate DATE; -- entity update date in db
386 l_log_module VARCHAR2(240);
387 BEGIN
388
389 IF g_log_enabled THEN
390 l_log_module := C_DEFAULT_MODULE||'.load_row';
391 END IF;
392
393 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
394 trace(p_msg => 'BEGIN of procedure load_row',
395 p_module => l_log_module,
396 p_level => C_LEVEL_PROCEDURE);
397 END IF;
398
399 -- Translate owner to file_last_updated_by
400 f_luby := fnd_load_util.owner_id(p_owner);
401
402 -- Translate char last_update_date to date
403 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
404
405 OPEN c_app_id(p_application_short_name);
406 FETCH c_app_id INTO l_application_id;
407 CLOSE c_app_id;
408
409 OPEN c_app_id(p_source_app_short_name);
410 FETCH c_app_id INTO l_source_app_id;
411 CLOSE c_app_id;
412
413 BEGIN
414
415 SELECT last_updated_by, last_update_date
416 INTO db_luby, db_ludate
417 FROM xla_event_sources
418 WHERE application_id = l_application_id
419 AND entity_code = p_entity_code
420 AND event_class_code = p_event_class_code
421 AND source_application_id= l_source_app_id
422 AND source_type_code = p_source_type_code
423 AND source_code = p_source_code;
424
425 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, NULL)) THEN
426 xla_event_sources_f_pkg.update_row
427 (x_rowid => l_rowid
428 ,x_application_id => l_application_id
429 ,x_entity_code => p_entity_code
430 ,x_event_class_code => p_event_class_code
431 ,x_source_application_id => l_source_app_id
432 ,x_source_type_code => p_source_type_code
433 ,x_source_code => p_source_code
434 ,x_active_flag => p_active_flag
435 ,x_level_code => p_level_code
436 ,x_last_update_date => f_ludate
437 ,x_last_updated_by => f_luby
438 ,x_last_update_login => 0);
439
440 END IF;
441
442 EXCEPTION
443 WHEN NO_DATA_FOUND THEN
444 xla_event_sources_f_pkg.insert_row
445 (x_rowid => l_rowid
446 ,x_application_id => l_application_id
447 ,x_entity_code => p_entity_code
448 ,x_event_class_code => p_event_class_code
449 ,x_source_application_id => l_source_app_id
450 ,x_source_type_code => p_source_type_code
451 ,x_source_code => p_source_code
452 ,x_active_flag => p_active_flag
453 ,x_level_code => p_level_code
454 ,x_creation_date => f_ludate
455 ,x_created_by => f_luby
456 ,x_last_update_date => f_ludate
457 ,x_last_updated_by => f_luby
458 ,x_last_update_login => 0);
459
460 END;
461
462 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
463 trace(p_msg => 'END of procedure load_row',
464 p_module => l_log_module,
465 p_level => C_LEVEL_PROCEDURE);
466 END IF;
467
468 EXCEPTION
469 WHEN NO_DATA_FOUND THEN
470 null;
471 WHEN OTHERS THEN
472 xla_exceptions_pkg.raise_message
473 (p_location => 'xla_event_sources_f_pkg.load_row');
474
475 END load_row;
476
477 --=============================================================================
478 --
479 -- Following code is executed when the package body is referenced for the first
480 -- time
481 --
482 --=============================================================================
483 BEGIN
487 ,module => C_DEFAULT_MODULE);
484 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
485 g_log_enabled := fnd_log.test
486 (log_level => g_log_level
488
489 IF NOT g_log_enabled THEN
490 g_log_level := C_LEVEL_LOG_DISABLED;
491 END IF;
492
493 end xla_event_sources_f_PKG;