[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 2006/08/25 20:53:16 weshen noship $ */
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_join_condition IN VARCHAR2
269 ,x_always_populated_flag IN VARCHAR2
270 ,x_last_update_date IN DATE
271 ,x_last_updated_by IN NUMBER
272 ,x_last_update_login IN NUMBER)
273
274 IS
275
276 l_log_module VARCHAR2(240);
277 BEGIN
278
279 IF g_log_enabled THEN
280 l_log_module := C_DEFAULT_MODULE||'.update_row';
281 END IF;
282
283 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
284 trace(p_msg => 'BEGIN of procedure update_row',
285 p_module => l_log_module,
286 p_level => C_LEVEL_PROCEDURE);
287 END IF;
288
289 UPDATE xla_reference_objects
290 SET
291 last_update_date = x_last_update_date
292 ,join_condition = x_join_condition
293 ,always_populated_flag = x_always_populated_flag
294 ,last_updated_by = x_last_updated_by
295 ,last_update_login = x_last_update_login
296 WHERE application_id = x_application_id
297 AND entity_code = x_entity_code
298 AND event_class_code = x_event_class_code
299 AND object_name = x_object_name
300 AND reference_object_appl_id = x_reference_object_appl_id
301 AND reference_object_name = x_reference_object_name
302 ;
303
304 IF (SQL%NOTFOUND) THEN
305 RAISE NO_DATA_FOUND;
306 END IF;
307
308 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
309 trace(p_msg => 'END of procedure update_row',
310 p_module => l_log_module,
311 p_level => C_LEVEL_PROCEDURE);
312 END IF;
313
314 END update_row;
315
316 /*======================================================================+
317 | |
318 | Procedure delete_row |
319 | |
320 +======================================================================*/
321 PROCEDURE delete_row
322 (x_application_id IN NUMBER
323 ,x_entity_code IN VARCHAR2
324 ,x_event_class_code IN VARCHAR2
325 ,x_object_name IN VARCHAR2
326 ,x_reference_object_appl_id IN NUMBER
327 ,x_reference_object_name IN VARCHAR2)
328
329 IS
330
331 l_log_module VARCHAR2(240);
332 BEGIN
333
334 IF g_log_enabled THEN
335 l_log_module := C_DEFAULT_MODULE||'.delete_row';
336 END IF;
337
338 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
339 trace(p_msg => 'BEGIN of procedure delete_row',
340 p_module => l_log_module,
341 p_level => C_LEVEL_PROCEDURE);
342 END IF;
343
344 DELETE
345 FROM xla_reference_objects
346 WHERE application_id = x_application_id
347 AND entity_code = x_entity_code
348 AND event_class_code = x_event_class_code
349 AND object_name = x_object_name
350 AND reference_object_appl_id = x_reference_object_appl_id
351 AND reference_object_name = x_reference_object_name
352 ;
353
354 IF (SQL%NOTFOUND) THEN
355 RAISE NO_DATA_FOUND;
356 END IF;
357
358 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
359 trace(p_msg => 'END of procedure delete_row',
360 p_module => l_log_module,
361 p_level => C_LEVEL_PROCEDURE);
362 END IF;
363
364 END delete_row;
365
369 -- Description: To be used by FNDLOAD to upload a row to the table
366 --=============================================================================
367 --
368 -- Name: load_row
370 --
371 --=============================================================================
372 PROCEDURE load_row
373 (p_application_short_name IN VARCHAR2
374 ,p_entity_code IN VARCHAR2
375 ,p_event_class_code IN VARCHAR2
376 ,p_object_name IN VARCHAR2
377 ,p_reference_object_appl_id IN NUMBER
378 ,p_reference_object_name IN VARCHAR2
379 ,p_linked_to_ref_obj_appl_id IN NUMBER
380 ,p_linked_to_ref_obj_name IN VARCHAR2
381 ,p_join_condition IN VARCHAR2
382 ,p_always_populated_flag IN VARCHAR2
383 ,p_owner IN VARCHAR2
384 ,p_last_update_date IN VARCHAR2)
385 IS
386 CURSOR c_app_id(p_app_short_name VARCHAR2) IS
387 SELECT application_id
388 FROM fnd_application
389 WHERE application_short_name = p_app_short_name;
390
391 l_application_id INTEGER;
392 l_rowid ROWID;
393 l_exist VARCHAR2(1);
394 f_luby NUMBER; -- entity owner in file
395 f_ludate DATE; -- entity update date in file
396 db_luby NUMBER; -- entity owner in db
397 db_ludate DATE; -- entity update date in db
398 l_log_module VARCHAR2(240);
399 BEGIN
400
401 IF g_log_enabled THEN
402 l_log_module := C_DEFAULT_MODULE||'.load_row';
403 END IF;
404
405 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
406 trace(p_msg => 'BEGIN of procedure load_row',
407 p_module => l_log_module,
408 p_level => C_LEVEL_PROCEDURE);
409 END IF;
410
411 -- Translate owner to file_last_updated_by
412 f_luby := fnd_load_util.owner_id(p_owner);
413
414 -- Translate char last_update_date to date
415 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
416
417 OPEN c_app_id(p_application_short_name);
418 FETCH c_app_id INTO l_application_id;
419 CLOSE c_app_id;
420
421 BEGIN
422
423 SELECT last_updated_by, last_update_date
424 INTO db_luby, db_ludate
425 FROM xla_reference_objects
426 WHERE application_id = l_application_id
427 AND entity_code = p_entity_code
428 AND event_class_code = p_event_class_code
429 AND object_name = p_object_name
430 AND reference_object_appl_id = p_reference_object_appl_id
431 AND reference_object_name = p_reference_object_name;
432
433 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, NULL)) THEN
434 xla_reference_objects_f_pkg.update_row
435 (x_application_id => l_application_id
436 ,x_entity_code => p_entity_code
437 ,x_event_class_code => p_event_class_code
438 ,x_object_name => p_object_name
439 ,x_reference_object_appl_id => p_reference_object_appl_id
440 ,x_reference_object_name => p_reference_object_name
441 ,x_join_condition => p_join_condition
442 ,x_always_populated_flag => p_always_populated_flag
443 ,x_last_update_date => f_ludate
444 ,x_last_updated_by => f_luby
445 ,x_last_update_login => 0);
446
447 END IF;
448
449 EXCEPTION
450 WHEN NO_DATA_FOUND THEN
451 xla_reference_objects_f_pkg.insert_row
452 (x_rowid => l_rowid
453 ,x_application_id => l_application_id
454 ,x_entity_code => p_entity_code
455 ,x_event_class_code => p_event_class_code
456 ,x_object_name => p_object_name
457 ,x_reference_object_appl_id => p_reference_object_appl_id
458 ,x_reference_object_name => p_reference_object_name
459 ,x_linked_to_ref_obj_appl_id => p_linked_to_ref_obj_appl_id
460 ,x_linked_to_ref_obj_name => p_linked_to_ref_obj_name
461 ,x_join_condition => p_join_condition
462 ,x_always_populated_flag => p_always_populated_flag
463 ,x_creation_date => f_ludate
464 ,x_created_by => f_luby
465 ,x_last_update_date => f_ludate
466 ,x_last_updated_by => f_luby
467 ,x_last_update_login => 0);
468
469 END;
470
471 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
472 trace(p_msg => 'END of procedure load_row',
473 p_module => l_log_module,
474 p_level => C_LEVEL_PROCEDURE);
475 END IF;
476
477 EXCEPTION
478 WHEN NO_DATA_FOUND THEN
479 null;
480 WHEN OTHERS THEN
481 xla_exceptions_pkg.raise_message
482 (p_location => 'xla_reference_objects_f_pkg.load_row');
483
484 END load_row;
485
486 --=============================================================================
487 --
488 -- Following code is executed when the package body is referenced for the first
489 -- time
490 --
491 --=============================================================================
492 BEGIN
493 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
494 g_log_enabled := fnd_log.test
495 (log_level => g_log_level
499 g_log_level := C_LEVEL_LOG_DISABLED;
496 ,module => C_DEFAULT_MODULE);
497
498 IF NOT g_log_enabled THEN
500 END IF;
501
502
503 END xla_reference_objects_f_pkg;