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