[Home] [Help]
PACKAGE BODY: APPS.XLA_EXTRACT_OBJECTS_F_PKG
Source
1 PACKAGE BODY xla_extract_objects_f_pkg AS
2 /* $Header: xlatbexo.pkb 120.7 2005/04/28 18:45:42 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_extract_objects |
10 | |
11 | DESCRIPTION |
12 | Forms PL/SQL Wrapper for xla_extract_objects |
13 | |
14 | HISTORY |
15 | Generated from XLAUTB. |
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_extract_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_extract_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_object_type_code IN VARCHAR2
75 ,x_always_populated_flag IN VARCHAR2
76 ,x_creation_date IN DATE
77 ,x_created_by IN NUMBER
78 ,x_last_update_date IN DATE
79 ,x_last_updated_by IN NUMBER
80 ,x_last_update_login IN NUMBER)
81
82 IS
83
84 CURSOR c IS
85 SELECT rowid
86 FROM xla_extract_objects
87 WHERE application_id = x_application_id
88 AND entity_code = x_entity_code
89 AND event_class_code = x_event_class_code
90 AND object_name = x_object_name
91 ;
92
93 l_log_module VARCHAR2(240);
94 BEGIN
95
96 IF g_log_enabled THEN
97 l_log_module := C_DEFAULT_MODULE||'.insert_row';
98 END IF;
99
100 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
101 trace(p_msg => 'BEGIN of procedure insert_row',
102 p_module => l_log_module,
103 p_level => C_LEVEL_PROCEDURE);
104 END IF;
105
106 INSERT INTO xla_extract_objects
107 (creation_date
108 ,created_by
109 ,application_id
110 ,entity_code
111 ,event_class_code
112 ,object_name
113 ,object_type_code
114 ,always_populated_flag
115 ,last_update_date
116 ,last_updated_by
117 ,last_update_login)
118 VALUES
119 (x_creation_date
120 ,x_created_by
121 ,x_application_id
122 ,x_entity_code
123 ,x_event_class_code
124 ,x_object_name
125 ,x_object_type_code
126 ,x_always_populated_flag
127 ,x_last_update_date
128 ,x_last_updated_by
129 ,x_last_update_login)
130 ;
131
132 OPEN c;
133 FETCH c INTO x_rowid;
134
135 IF (c%NOTFOUND) THEN
136 CLOSE c;
137 RAISE NO_DATA_FOUND;
138 END IF;
139 CLOSE c;
140
141 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
142 trace(p_msg => 'END of procedure insert_row',
143 p_module => l_log_module,
144 p_level => C_LEVEL_PROCEDURE);
145 END IF;
146
147 END insert_row;
148
149 /*======================================================================+
150 | |
151 | Procedure lock_row |
152 | |
153 +======================================================================*/
154 PROCEDURE lock_row
155 (x_application_id IN NUMBER
156 ,x_entity_code IN VARCHAR2
157 ,x_event_class_code IN VARCHAR2
158 ,x_object_name IN VARCHAR2
159 ,x_object_type_code IN VARCHAR2
160 ,x_always_populated_flag IN VARCHAR2)
161
162 IS
163
164 CURSOR c IS
165 SELECT application_id
166 ,entity_code
167 ,event_class_code
168 ,object_name
169 ,object_type_code
170 ,always_populated_flag
171 FROM xla_extract_objects
172 WHERE application_id = x_application_id
173 AND entity_code = x_entity_code
174 AND event_class_code = x_event_class_code
175 AND object_name = x_object_name
176 FOR UPDATE OF application_id NOWAIT;
177
178 recinfo c%ROWTYPE;
179
180 l_log_module VARCHAR2(240);
181 BEGIN
182
183 IF g_log_enabled THEN
184 l_log_module := C_DEFAULT_MODULE||'.lock_row';
185 END IF;
186
187 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
188 trace(p_msg => 'BEGIN of procedure lock_row',
189 p_module => l_log_module,
190 p_level => C_LEVEL_PROCEDURE);
191 END IF;
192
193 OPEN c;
194 FETCH c INTO recinfo;
195
196 IF (c%NOTFOUND) THEN
197 CLOSE c;
198 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
199 app_exception.raise_exception;
200 END IF;
201 CLOSE c;
202
203 IF ( (recinfo.application_id = x_application_id)
204 AND (recinfo.entity_code = x_entity_code)
205 AND (recinfo.event_class_code = x_event_class_code)
206 AND (recinfo.object_name = x_object_name)
207 AND (recinfo.object_type_code = x_object_type_code)
208 AND (recinfo.always_populated_flag = x_always_populated_flag)
209 ) THEN
210 NULL;
211 ELSE
212 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
213 app_exception.raise_exception;
214 END IF;
215
216 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
217 trace(p_msg => 'END of procedure lock_row',
218 p_module => l_log_module,
219 p_level => C_LEVEL_PROCEDURE);
220 END IF;
221
222 END lock_row;
223
224 /*======================================================================+
225 | |
226 | Procedure update_row |
227 | |
228 +======================================================================*/
229 PROCEDURE update_row
230 (x_application_id IN NUMBER
231 ,x_entity_code IN VARCHAR2
232 ,x_event_class_code IN VARCHAR2
233 ,x_object_name IN VARCHAR2
234 ,x_object_type_code IN VARCHAR2
235 ,x_always_populated_flag IN VARCHAR2
236 ,x_last_update_date IN DATE
237 ,x_last_updated_by IN NUMBER
238 ,x_last_update_login IN NUMBER)
239
240 IS
241
242 l_log_module VARCHAR2(240);
243 BEGIN
244
245 IF g_log_enabled THEN
246 l_log_module := C_DEFAULT_MODULE||'.update_row';
247 END IF;
248
249 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
250 trace(p_msg => 'BEGIN of procedure update_row',
251 p_module => l_log_module,
252 p_level => C_LEVEL_PROCEDURE);
253 END IF;
254
255 UPDATE xla_extract_objects
256 SET
257 last_update_date = x_last_update_date
258 ,object_type_code = x_object_type_code
259 ,always_populated_flag = x_always_populated_flag
260 ,last_updated_by = x_last_updated_by
261 ,last_update_login = x_last_update_login
262 WHERE application_id = x_application_id
263 AND entity_code = x_entity_code
264 AND event_class_code = x_event_class_code
265 AND object_name = x_object_name
266 ;
267
268 IF (SQL%NOTFOUND) THEN
269 RAISE NO_DATA_FOUND;
270 END IF;
271
272 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
273 trace(p_msg => 'END of procedure update_row',
274 p_module => l_log_module,
275 p_level => C_LEVEL_PROCEDURE);
276 END IF;
277
278 END update_row;
279
280 /*======================================================================+
281 | |
282 | Procedure delete_row |
283 | |
284 +======================================================================*/
285 PROCEDURE delete_row
286 (x_application_id IN NUMBER
287 ,x_entity_code IN VARCHAR2
288 ,x_event_class_code IN VARCHAR2
289 ,x_object_name IN VARCHAR2)
290
291 IS
292
293 l_log_module VARCHAR2(240);
294 BEGIN
295
296 IF g_log_enabled THEN
297 l_log_module := C_DEFAULT_MODULE||'.delete_row';
298 END IF;
299
300 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
301 trace(p_msg => 'BEGIN of procedure delete_row',
302 p_module => l_log_module,
303 p_level => C_LEVEL_PROCEDURE);
304 END IF;
305
306 DELETE FROM xla_extract_objects
307 WHERE application_id = x_application_id
308 AND entity_code = x_entity_code
309 AND event_class_code = x_event_class_code
310 AND object_name = x_object_name
311 ;
312
313
314 IF (SQL%NOTFOUND) THEN
315 RAISE NO_DATA_FOUND;
316 END IF;
317
318 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
319 trace(p_msg => 'END of procedure delete_row',
320 p_module => l_log_module,
321 p_level => C_LEVEL_PROCEDURE);
322 END IF;
323
324 END delete_row;
325
326 --=============================================================================
327 --
328 -- Name: load_row
329 -- Description: To be used by FNDLOAD to upload a row to the table
330 --
331 --=============================================================================
332 PROCEDURE load_row
333 (p_application_short_name IN VARCHAR2
334 ,p_entity_code IN VARCHAR2
335 ,p_event_class_code IN VARCHAR2
336 ,p_object_name IN VARCHAR2
337 ,p_object_type_code IN VARCHAR2
338 ,p_always_populated_flag IN VARCHAR2
339 ,p_owner IN VARCHAR2
340 ,p_last_update_date IN VARCHAR2)
341 IS
342 CURSOR c_app_id(p_app_short_name VARCHAR2) IS
343 SELECT application_id
344 FROM fnd_application
345 WHERE application_short_name = p_app_short_name;
346
347 l_application_id INTEGER;
348 l_rowid ROWID;
349 l_exist VARCHAR2(1);
350 f_luby NUMBER; -- entity owner in file
351 f_ludate DATE; -- entity update date in file
352 db_luby NUMBER; -- entity owner in db
353 db_ludate DATE; -- entity update date in db
354 l_log_module VARCHAR2(240);
355 BEGIN
356
357 IF g_log_enabled THEN
358 l_log_module := C_DEFAULT_MODULE||'.load_row';
359 END IF;
360
361 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
362 trace(p_msg => 'BEGIN of procedure load_row',
363 p_module => l_log_module,
364 p_level => C_LEVEL_PROCEDURE);
365 END IF;
366
367 -- Translate owner to file_last_updated_by
368 f_luby := fnd_load_util.owner_id(p_owner);
369
370 -- Translate char last_update_date to date
371 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
372
373 OPEN c_app_id(p_application_short_name);
374 FETCH c_app_id INTO l_application_id;
375 CLOSE c_app_id;
376
377 BEGIN
378
379 SELECT last_updated_by, last_update_date
380 INTO db_luby, db_ludate
381 FROM xla_extract_objects
382 WHERE application_id = l_application_id
383 AND entity_code = p_entity_code
384 AND event_class_code = p_event_class_code
385 AND object_name = p_object_name;
386
387 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, NULL)) THEN
388 xla_extract_objects_f_pkg.update_row
389 (x_application_id => l_application_id
390 ,x_entity_code => p_entity_code
391 ,x_event_class_code => p_event_class_code
392 ,x_object_name => p_object_name
393 ,x_object_type_code => p_object_type_code
394 ,x_always_populated_flag => p_always_populated_flag
395 ,x_last_update_date => f_ludate
396 ,x_last_updated_by => f_luby
397 ,x_last_update_login => 0);
398
399 END IF;
400
401 EXCEPTION
402 WHEN NO_DATA_FOUND THEN
403 xla_extract_objects_f_pkg.insert_row
404 (x_rowid => l_rowid
405 ,x_application_id => l_application_id
406 ,x_entity_code => p_entity_code
407 ,x_event_class_code => p_event_class_code
408 ,x_object_name => p_object_name
409 ,x_object_type_code => p_object_type_code
410 ,x_always_populated_flag => p_always_populated_flag
411 ,x_creation_date => f_ludate
412 ,x_created_by => f_luby
413 ,x_last_update_date => f_ludate
414 ,x_last_updated_by => f_luby
415 ,x_last_update_login => 0);
416
417 END;
418
419 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
420 trace(p_msg => 'END of procedure load_row',
421 p_module => l_log_module,
422 p_level => C_LEVEL_PROCEDURE);
423 END IF;
424
425 EXCEPTION
426 WHEN NO_DATA_FOUND THEN
427 null;
428 WHEN OTHERS THEN
429 xla_exceptions_pkg.raise_message
430 (p_location => 'xla_extract_objects_f_pkg.load_row');
431
432 END load_row;
433
434 --=============================================================================
435 --
436 -- Following code is executed when the package body is referenced for the first
437 -- time
438 --
439 --=============================================================================
440 BEGIN
441 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
442 g_log_enabled := fnd_log.test
443 (log_level => g_log_level
444 ,module => C_DEFAULT_MODULE);
445
446 IF NOT g_log_enabled THEN
447 g_log_level := C_LEVEL_LOG_DISABLED;
448 END IF;
449
450
451 end xla_extract_objects_f_pkg;