[Home] [Help]
PACKAGE BODY: APPS.VEA_PARAMETERS_SV
Source
1 PACKAGE BODY VEA_PARAMETERS_SV as
2 /* $Header: VEAVAPAB.pls 115.12 2004/07/27 02:42:20 rvishnuv ship $ */
3 --{
4 /*====================== vea_parameters_sv =========================*/
5 /*========================================================================
6 PURPOSE: Table handler package for table VEA_PARAMETERS
7
8 NOTES: To run the script:
9
10 sql> start VEAVAPKB.pls
11
12 HISTORY
13 Created N PARIKH 09/09/99 10:00 AM
14
15 =========================================================================*/
16
17 G_PACKAGE_NAME CONSTANT VARCHAR2(30) := 'VEA_PARAMETERS_SV';
18 --
19 --
20 /*========================================================================
21
22 PROCEDURE NAME: insert_row
23
24 PURPOSE: Inserts a record into VEA_PARAMETERS table
25
26 ========================================================================*/
27 PROCEDURE
28 insert_row
29 (
30 p_layer_provider_code IN vea_parameters.layer_provider_code%TYPE,
31 p_parameter_id IN vea_parameters.parameter_id%TYPE,
32 p_program_unit_id IN vea_parameters.program_unit_id%TYPE,
33 p_parameter_type IN vea_parameters.parameter_type%TYPE,
34 p_parameter_seq IN vea_parameters.parameter_seq%TYPE,
35 p_name IN vea_parameters.name%TYPE,
36 p_datatype IN vea_parameters.datatype%TYPE,
37 p_default_value IN vea_parameters.default_value%TYPE,
38 p_description IN vea_parameters.description%TYPE
39 )
40 IS
41 --{
42 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
43 l_location VARCHAR2(32767);
44 --
45 --
46 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
47 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
48 --}
49 BEGIN
50 --{
51 l_location := '0010';
52 --
53 INSERT INTO vea_parameters
54 (
55 layer_provider_code, parameter_id,
56 program_unit_id, parameter_type,
57 name, parameter_seq,
58 datatype, default_value,
59 description,
60 created_by, creation_date,
61 last_updated_by, last_update_date,
62 last_update_login
63 )
64 VALUES
65 (
66 p_layer_provider_code, p_parameter_id,
67 p_program_unit_id, p_parameter_type,
68 p_name, p_parameter_seq,
69 p_datatype, p_default_value,
70 p_description,
71 l_user_id, SYSDATE,
72 l_user_id, SYSDATE,
73 l_login_id
74 );
75 --}
76 EXCEPTION
77 --{
78 WHEN OTHERS
79 THEN
80 --{
81 vea_tpa_util_pvt.add_exc_message_and_raise
82 (
83 p_package_name => G_PACKAGE_NAME,
84 p_api_name => l_api_name,
85 p_location => l_location
86 );
87 --}
88 --}
89 END insert_row;
90 --
91 --
92 /*========================================================================
93
94 PROCEDURE NAME: update_row
95
96 PURPOSE: Updates a record into VEA_PARAMETERS table
97
98 ========================================================================*/
99 PROCEDURE
100 update_row
101 (
102 p_layer_provider_code IN vea_parameters.layer_provider_code%TYPE,
103 p_parameter_id IN vea_parameters.parameter_id%TYPE,
104 p_program_unit_id IN vea_parameters.program_unit_id%TYPE,
105 p_parameter_type IN vea_parameters.parameter_type%TYPE,
106 p_parameter_seq IN vea_parameters.parameter_seq%TYPE,
107 p_name IN vea_parameters.name%TYPE,
108 p_datatype IN vea_parameters.datatype%TYPE,
109 p_default_value IN vea_parameters.default_value%TYPE,
110 p_description IN vea_parameters.description%TYPE
111 )
112 IS
113 --{
114 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
115 l_location VARCHAR2(32767);
116 --
117 --
118 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
119 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
120 --}
121 BEGIN
122 --{
123 l_location := '0010';
124 --
125 UPDATE vea_parameters
126 SET program_unit_id = p_program_unit_id,
127 parameter_type = p_parameter_type,
128 parameter_seq = p_parameter_seq,
129 name = p_name,
130 datatype = p_datatype,
131 default_value = p_default_value,
132 description = p_description,
133 last_updated_by = l_user_id,
134 last_update_date = SYSDATE,
135 last_update_login = l_login_id
136 WHERE layer_provider_code = p_layer_provider_code
137 AND parameter_id = p_parameter_id;
138 --}
139 EXCEPTION
140 --{
141 WHEN OTHERS
142 THEN
143 --{
144 vea_tpa_util_pvt.add_exc_message_and_raise
145 (
146 p_package_name => G_PACKAGE_NAME,
147 p_api_name => l_api_name,
148 p_location => l_location
149 );
150 --}
151 --}
152 END update_row;
153 --
154 --
155 /*========================================================================
156
157 PROCEDURE NAME: delete_rows
158
159 PURPOSE: Deletes all parameters for the specified program unit
160
161 ========================================================================*/
162 PROCEDURE
163 delete_rows
164 (
165 p_layer_provider_code IN vea_parameters.layer_provider_code%TYPE,
166 p_program_unit_id IN vea_parameters.program_unit_id%TYPE
167 )
168 IS
169 --{
170 l_api_name CONSTANT VARCHAR2(30) := 'delete_rows';
171 l_location VARCHAR2(32767);
172 --
173 --
174 --}
175 BEGIN
176 --{
177 l_location := '0010';
178 --
179 DELETE vea_parameters
180 WHERE layer_provider_code = p_layer_provider_code
181 AND program_unit_id = p_program_unit_id;
182 --}
183 EXCEPTION
184 --{
185 WHEN OTHERS
186 THEN
187 --{
188 vea_tpa_util_pvt.add_exc_message_and_raise
189 (
190 p_package_name => G_PACKAGE_NAME,
191 p_api_name => l_api_name,
192 p_location => l_location
193 );
194 --}
195 --}
196 END delete_rows;
197 --
198 --
199 --
200 /*========================================================================
201
202 PROCEDURE NAME: getId
203
204 PURPOSE:
205
206 ========================================================================*/
207 FUNCTION
208 getId
209 (
210 p_layer_provider_code IN vea_parameters.layer_provider_code%TYPE,
211 p_program_unit_id IN vea_parameters.program_unit_id%TYPE,
212 p_name IN vea_parameters.name%TYPE
213 )
214 RETURN NUMBER
215 IS
216 --{
217 l_api_name CONSTANT VARCHAR2(30) := 'getId';
218 l_location VARCHAR2(32767);
219 --
220 --
221 CURSOR parameter_cur
222 (
223 p_layer_provider_code IN vea_parameters.layer_provider_code%TYPE,
224 p_program_unit_id IN vea_parameters.program_unit_id%TYPE,
225 p_name IN vea_parameters.name%TYPE
226 )
227 IS
228 SELECT parameter_id
229 FROM vea_parameters
230 WHERE layer_provider_code = p_layer_provider_code
231 AND program_unit_id = p_program_unit_id
232 AND upper(name) = upper(p_name);
233 --}
234 BEGIN
235 --{
236 l_location := '0010';
237 --
238 FOR parameter_rec IN parameter_cur
239 (
240 p_layer_provider_code => p_layer_provider_code,
241 p_program_unit_id => p_program_unit_id,
242 p_name => p_name
243 )
244 LOOP
245 --{
246 l_location := '0020';
247 --
248 RETURN (parameter_rec.parameter_id);
249 --}
250 END LOOP;
251 --
252 --
253 RETURN(NULL);
254 --}
255 EXCEPTION
256 --{
257 WHEN OTHERS
258 THEN
259 --{
260 RAISE;
261 --}
262 --}
263 END getId;
264 --
265 --
266 /*========================================================================
267
268 PROCEDURE NAME: process
269
270 PURPOSE: Table hadndler API for VEA_PARAMETERS table.
271
272 It inserts/updates a record in VEA_PARAMETERS table.
273
274 ========================================================================*/
275 PROCEDURE
276 process
277 (
278 p_api_version IN NUMBER,
279 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
280 p_commit IN VARCHAR2 := FND_API.G_FALSE,
281 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
282 x_return_status OUT NOCOPY VARCHAR2,
283 x_msg_count OUT NOCOPY NUMBER,
284 x_msg_data OUT NOCOPY VARCHAR2,
285 x_id OUT NOCOPY vea_parameters.parameter_id%TYPE,
286 p_layer_provider_code IN vea_parameters.layer_provider_code%TYPE,
287 p_program_unit_id IN vea_parameters.program_unit_id%TYPE,
288 p_parameter_type IN vea_parameters.parameter_type%TYPE,
289 p_parameter_seq IN vea_parameters.parameter_seq%TYPE,
290 p_name IN vea_parameters.name%TYPE,
291 p_datatype IN vea_parameters.datatype%TYPE,
292 p_default_value IN vea_parameters.default_value%TYPE,
293 p_description IN vea_parameters.description%TYPE,
294 p_id IN vea_parameters.parameter_id%TYPE := NULL,
295 p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE := NULL
296 )
297 IS
298 --{
299 l_api_name CONSTANT VARCHAR2(30) := 'process';
300 l_api_version CONSTANT NUMBER := 1.0;
301 l_api_type CONSTANT VARCHAR2(3) := vea_tpa_util_pvt.G_PUBLIC_API;
302 --
303 --
304 l_location VARCHAR2(32767);
305 l_savepoint_name VARCHAR2(30);
306 l_parameter_id vea_parameters.parameter_id%TYPE;
307 l_program_unit_id vea_program_units.program_unit_id%TYPE;
308 l_tp_layer_id vea_tp_layers.tp_layer_id%TYPE;
309 --
310 --
311 CURSOR parameter_cur
312 (
313 p_layer_provider_code IN vea_parameters.layer_provider_code%TYPE,
314 p_parameter_id IN vea_parameters.parameter_id%TYPE,
315 p_program_unit_id IN vea_parameters.program_unit_id%TYPE,
316 p_name IN vea_parameters.name%TYPE
317 )
318 IS
319 SELECT parameter_id
320 FROM vea_parameters
321 WHERE layer_provider_code = p_layer_provider_code
322 AND program_unit_id = p_program_unit_id
323 AND name = p_name;
324 -- Commented out this code because we no longer base our processing on the ids
325 -- stored in the flat file. We go by the names of the packages
326 -- and derive the ids based on the names and use the derived ids
327 -- in further processing.
328 /*
329 AND (
330 (
331 p_parameter_id IS NOT NULL
332 AND parameter_id = p_parameter_id
333 )
334 OR
335 (
336 p_parameter_id IS NULL
337 AND program_unit_id = p_program_unit_id
338 AND name = p_name
339 )
340 );
341 */
342 --
343 --
344 --}
345 BEGIN
346 --{
347 l_location := '0010';
348 --
349 IF NOT( vea_tpa_util_pvt.is_vea_installed() )
350 THEN
351 RETURN;
352 END IF;
353 --
354 --
355 -- Standard API Header
356 --
357 l_location := '0020';
358 --
359 vea_tpa_util_pvt.api_header
360 (
361 p_package_name => G_PACKAGE_NAME,
362 p_api_name => l_api_name,
363 p_api_type => l_api_type,
364 p_api_current_version => l_api_version,
365 p_api_caller_version => p_api_version,
366 p_init_msg_list => p_init_msg_list,
367 x_savepoint_name => l_savepoint_name,
368 x_api_return_status => x_return_status
369 );
370 --
371 --
372 --{ API Body
373 --
374 --
375 l_location := '0030';
376 --
377 IF (VEA_TPA_UTIL_PVT.isLayerMergeOn) THEN
378 --{
379 l_program_unit_id := VEA_PROGRAM_UNITS_SV.g_program_unit_id;
380 l_tp_layer_id := nvl(vea_packages_sv.g_tp_layer_id,p_tp_layer_id);
381 --}
382 ELSE
383 --{
384 l_program_unit_id := p_program_unit_id;
385 l_tp_layer_id := p_tp_layer_id;
386 --}
387 END IF;
388 --
389 --
390 l_location := '0035';
391 --
392 --
393 IF vea_layer_licenses_sv.isLicensed
394 (
395 p_layer_provider_code => p_layer_provider_code,
396 p_tp_layer_id => l_tp_layer_id
397 )
398 THEN
399 --{
400 --
401 --
402 l_location := '0040';
403 --
404 --
405 l_parameter_id := NULL;
406 --
407 --
408 l_parameter_id := getId
409 (
410 p_layer_provider_code => p_layer_provider_code,
411 p_program_unit_id => l_program_unit_id,
412 p_name => p_name
413 );
414 --
415 --
416 l_location := '0060';
417 --
418 IF l_parameter_id IS NULL
419 THEN
420 --{
421 l_location := '0070';
422 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_layer_provider_code is ' || p_layer_provider_code);
423 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' l_parameter_id is ' || l_parameter_id);
424 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' l_program_unit_id is ' || l_program_unit_id);
425 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_parameter_type is ' || p_parameter_type);
426 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_parameter_seq is ' || p_parameter_seq);
427 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_name is ' || p_name);
428 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_datatype is ' || p_datatype);
429 --
430 --
431 IF p_layer_provider_code = vea_tpa_util_pvt.g_current_layer_provider_code
432 THEN
433 SELECT NVL( p_id, vea_parameters_s.NEXTVAL )
434 INTO l_parameter_id
435 FROM DUAL;
436 ELSE
437 SELECT vea_parameters_s.NEXTVAL
438 INTO l_parameter_id
439 FROM DUAL;
440 END IF;
441 --
442 --
443 --
444 l_location := '0080';
445 --
446 insert_row
447 (
448 p_layer_provider_code => p_layer_provider_code,
449 p_parameter_id => l_parameter_id,
450 p_program_unit_id => l_program_unit_id,
451 p_parameter_type => p_parameter_type,
452 p_parameter_seq => p_parameter_seq,
453 p_name => p_name,
454 p_datatype => p_datatype,
455 p_default_value => p_default_value,
456 p_description => p_description
457 );
458 --}
459 ELSE
460 --{
461 l_location := '0090';
462 --
463 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_layer_provider_code is ' || p_layer_provider_code);
464 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' l_parameter_id is ' || l_parameter_id);
465 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' l_program_unit_id is ' || l_program_unit_id);
466 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_parameter_type is ' || p_parameter_type);
467 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_parameter_seq is ' || p_parameter_seq);
468 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_name is ' || p_name);
469 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_datatype is ' || p_datatype);
470 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_default_value is ' || p_default_value);
471 update_row
472 (
473 p_layer_provider_code => p_layer_provider_code,
474 p_parameter_id => l_parameter_id,
475 p_program_unit_id => l_program_unit_id,
476 p_parameter_type => p_parameter_type,
477 p_parameter_seq => p_parameter_seq,
478 p_name => p_name,
479 p_datatype => p_datatype,
480 p_default_value => p_default_value,
481 p_description => p_description
482 );
483 --}
484 END IF;
485 --
486 --
487 l_location := '0100';
488 --
489 x_id := l_parameter_id;
490 --}
491 END IF;
492 --
493 --
494 --
495 --} API Body
496 --
497 --
498 -- Standard API Footer
499 --
500 l_location := '0110';
501 --
502 vea_tpa_util_pvt.api_footer
503 (
504 p_commit => p_commit,
505 x_msg_count => x_msg_count,
506 x_msg_data => x_msg_data
507 );
508 --}
509 EXCEPTION
510 --{
511 WHEN FND_API.G_EXC_ERROR
512 THEN
513 --{
514 --RAISE;
515 vea_tpa_util_pvt.handle_error
516 (
517 p_error_type => vea_tpa_util_pvt.G_ERROR,
518 p_savepoint_name => l_savepoint_name,
519 p_package_name => G_PACKAGE_NAME,
520 p_api_name => l_api_name,
521 p_location => l_location,
522 x_msg_count => x_msg_count,
523 x_msg_data => x_msg_data,
524 x_api_return_status => x_return_status
525 );
526 --}
527 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
528 THEN
529 --{
530 --RAISE;
531 vea_tpa_util_pvt.handle_error
532 (
533 p_error_type => vea_tpa_util_pvt.G_UNEXPECTED_ERROR,
534 p_savepoint_name => l_savepoint_name,
535 p_package_name => G_PACKAGE_NAME,
536 p_api_name => l_api_name,
537 p_location => l_location,
538 x_msg_count => x_msg_count,
539 x_msg_data => x_msg_data,
540 x_api_return_status => x_return_status
541 );
542 --}
543 WHEN OTHERS
544 THEN
545 --{
546 --RAISE;
547 vea_tpa_util_pvt.handle_error
548 (
549 p_error_type => vea_tpa_util_pvt.G_OTHER_ERROR,
550 p_savepoint_name => l_savepoint_name,
551 p_package_name => G_PACKAGE_NAME,
552 p_api_name => l_api_name,
553 p_location => l_location,
554 x_msg_count => x_msg_count,
555 x_msg_data => x_msg_data,
556 x_api_return_status => x_return_status
557 );
558 --}
559 --}
560 END process;
561 --}
562 END VEA_PARAMETERS_SV;