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