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