[Home] [Help]
PACKAGE BODY: APPS.VEA_PARAMETER_MAPPINGS_SV
Source
1 PACKAGE BODY VEA_PARAMETER_MAPPINGS_SV as
2 /* $Header: VEAVAPMB.pls 115.9 2004/07/27 00:52:54 rvishnuv ship $ */
3 --{
4 /*====================== vea_parameter_mappings_sv =========================*/
5 /*========================================================================
6 PURPOSE: Table handler package for table VEA_PARAMETER_MAPPINGS
7
8 NOTES: To run the script:
9
10 sql> start VEAVALHB.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_PARAMETER_MAPPINGS_SV';
18 --
19 --
20 /*========================================================================
21
22 PROCEDURE NAME: insert_row
23
24 PURPOSE: Inserts a record into VEA_PARAMETER_MAPPINGS table
25
26 ========================================================================*/
27 PROCEDURE
28 insert_row
29 (
30 p_layer_provider_code IN vea_parameter_mappings.layer_provider_code%TYPE,
31 p_parameter_mapping_id IN vea_parameter_mappings.parameter_mapping_id%TYPE,
32 p_layer_header_id IN vea_parameter_mappings.layer_header_id%TYPE,
33 p_program_unit_parameter_id IN vea_parameter_mappings.program_unit_parameter_id%TYPE,
34 p_program_unit_param_lp_code IN vea_parameter_mappings.program_unit_param_lp_code%TYPE,
35 p_tps_parameter_id IN vea_parameter_mappings.tps_parameter_id%TYPE,
36 p_tps_parameter_lp_code IN vea_parameter_mappings.tps_parameter_lp_code%TYPE,
37 p_description IN vea_parameter_mappings.description%TYPE
38 )
39 IS
40 --{
41 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
42 l_location VARCHAR2(32767);
43 --
44 --
45 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
46 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
47 --}
48 BEGIN
49 --{
50 l_location := '0010';
51 --
52 INSERT INTO vea_parameter_mappings
53 (
54 layer_provider_code, parameter_mapping_id,
55 layer_header_id,
56 program_unit_parameter_id, program_unit_param_lp_code,
57 tps_parameter_id, tps_parameter_lp_code,
58 description,
59 created_by, creation_date,
60 last_updated_by, last_update_date,
61 last_update_login
62 )
63 VALUES
64 (
65 p_layer_provider_code, p_parameter_mapping_id,
66 p_layer_header_id,
67 p_program_unit_parameter_id, p_program_unit_param_lp_code,
68 p_tps_parameter_id, p_tps_parameter_lp_code,
69 p_description,
70 l_user_id, SYSDATE,
71 l_user_id, SYSDATE,
72 l_login_id
73 );
74 --}
75 EXCEPTION
76 --{
77 WHEN OTHERS
78 THEN
79 --{
80 vea_tpa_util_pvt.add_exc_message_and_raise
81 (
82 p_package_name => G_PACKAGE_NAME,
83 p_api_name => l_api_name,
84 p_location => l_location
85 );
86 --}
87 --}
88 END insert_row;
89 --
90 --
91 /*========================================================================
92
93 PROCEDURE NAME: update_row
94
95 PURPOSE: Updates a record into VEA_PARAMETER_MAPPINGS table
96
97 ========================================================================*/
98 PROCEDURE
99 update_row
100 (
101 p_layer_provider_code IN vea_parameter_mappings.layer_provider_code%TYPE,
102 p_parameter_mapping_id IN vea_parameter_mappings.parameter_mapping_id%TYPE,
103 p_layer_header_id IN vea_parameter_mappings.layer_header_id%TYPE,
104 p_program_unit_parameter_id IN vea_parameter_mappings.program_unit_parameter_id%TYPE,
105 p_program_unit_param_lp_code IN vea_parameter_mappings.program_unit_param_lp_code%TYPE,
106 p_tps_parameter_id IN vea_parameter_mappings.tps_parameter_id%TYPE,
107 p_tps_parameter_lp_code IN vea_parameter_mappings.tps_parameter_lp_code%TYPE,
108 p_description IN vea_parameter_mappings.description%TYPE
109 )
110 IS
111 --{
112 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
113 l_location VARCHAR2(32767);
114 --
115 --
116 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
117 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
118 --}
119 BEGIN
120 --{
121 l_location := '0010';
122 --
123 UPDATE vea_parameter_mappings
124 SET program_unit_parameter_id = p_program_unit_parameter_id,
125 program_unit_param_lp_code = p_program_unit_param_lp_code,
126 layer_header_id = p_layer_header_id,
127 tps_parameter_id = p_tps_parameter_id,
128 tps_parameter_lp_code = p_tps_parameter_lp_code,
129 description = p_description,
130 last_updated_by = l_user_id,
131 last_update_date = SYSDATE,
132 last_update_login = l_login_id
133 WHERE layer_provider_code = p_layer_provider_code
134 AND parameter_mapping_id = p_parameter_mapping_id;
135 --AND layer_header_id = p_layer_header_id
136 --AND tps_parameter_id = p_tps_parameter_id
137 --AND tps_parameter_lp_code = p_tps_parameter_lp_code;
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 parameter mappings for the specified layer header.
160
161 ========================================================================*/
162 PROCEDURE
163 delete_rows
164 (
165 p_layer_provider_code IN vea_parameter_mappings.layer_provider_code%TYPE,
166 p_layer_header_id IN vea_parameter_mappings.layer_header_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_parameter_mappings
180 WHERE layer_provider_code = p_layer_provider_code
181 AND layer_header_id = p_layer_header_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 PROCEDURE NAME: process
202
203 PURPOSE: Table hadndler API for VEA_PARAMETER_MAPPINGS table.
204
205 It inserts/updates a record in VEA_PARAMETER_MAPPINGS table.
206
207 ========================================================================*/
208 PROCEDURE
209 process
210 (
211 p_api_version IN NUMBER,
212 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
213 p_commit IN VARCHAR2 := FND_API.G_FALSE,
214 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
215 x_return_status OUT NOCOPY VARCHAR2,
216 x_msg_count OUT NOCOPY NUMBER,
217 x_msg_data OUT NOCOPY VARCHAR2,
218 x_id OUT NOCOPY vea_parameter_mappings.parameter_mapping_id%TYPE,
219 p_layer_provider_code IN vea_parameter_mappings.layer_provider_code%TYPE,
220 p_layer_header_id IN vea_parameter_mappings.layer_header_id%TYPE,
221 p_tps_parameter_id IN vea_parameter_mappings.tps_parameter_id%TYPE,
222 p_tps_parameter_lp_code IN vea_parameter_mappings.tps_parameter_lp_code%TYPE,
223 p_program_unit_parameter_id IN vea_parameter_mappings.program_unit_parameter_id%TYPE,
224 p_program_unit_param_lp_code IN vea_parameter_mappings.program_unit_param_lp_code%TYPE,
225 p_description IN vea_parameter_mappings.description%TYPE,
226 p_id IN vea_parameter_mappings.parameter_mapping_id%TYPE := NULL,
227 p_program_unit_parameter_name IN vea_parameters.name%TYPE,
228 p_tps_parameter_name IN vea_parameters.name%TYPE
229
230 )
231 IS
232 --{
233 l_api_name CONSTANT VARCHAR2(30) := 'process';
234 l_api_version CONSTANT NUMBER := 1.0;
235 l_api_type CONSTANT VARCHAR2(3) := vea_tpa_util_pvt.G_PUBLIC_API;
236 --
237 --
238 l_location VARCHAR2(32767);
239 l_savepoint_name VARCHAR2(30);
240 l_parameter_mapping_id vea_parameter_mappings.parameter_mapping_id%TYPE;
241 l_program_unit_parameter_id vea_parameter_mappings.program_unit_parameter_id%TYPE;
242 l_tps_parameter_id vea_parameter_mappings.tps_parameter_id%TYPE;
243 l_layer_header_id vea_parameter_mappings.layer_header_id%TYPE;
244 --
245 --
246 CURSOR parameter_mapping_cur
247 (
248 p_layer_provider_code IN vea_parameter_mappings.layer_provider_code%TYPE,
249 p_parameter_mapping_id IN vea_parameter_mappings.parameter_mapping_id%TYPE,
250 p_layer_header_id IN vea_parameter_mappings.layer_header_id%TYPE,
251 p_tps_parameter_id IN vea_parameter_mappings.tps_parameter_id%TYPE,
252 p_tps_parameter_lp_code IN vea_parameter_mappings.tps_parameter_lp_code%TYPE
253 )
254 IS
255 SELECT parameter_mapping_id
256 FROM vea_parameter_mappings
257 WHERE layer_provider_code = p_layer_provider_code
258 --AND parameter_mapping_id = p_parameter_mapping_id;
259 --AND parameter_mapping_id = NVL(p_parameter_mapping_id,parameter_mapping_id)
260 AND layer_header_id = p_layer_header_id
261 AND tps_parameter_id = p_tps_parameter_id
262 AND tps_parameter_lp_code = p_tps_parameter_lp_code;
263 --
264 --
265 --}
266 BEGIN
267 --{
268 l_location := '0010';
269 --
270 IF NOT( vea_tpa_util_pvt.is_vea_installed() )
271 THEN
272 RETURN;
273 END IF;
274 --
275 --
276 -- Standard API Header
277 --
278 l_location := '0020';
279 --
280 vea_tpa_util_pvt.api_header
281 (
282 p_package_name => G_PACKAGE_NAME,
283 p_api_name => l_api_name,
284 p_api_type => l_api_type,
285 p_api_current_version => l_api_version,
286 p_api_caller_version => p_api_version,
287 p_init_msg_list => p_init_msg_list,
288 x_savepoint_name => l_savepoint_name,
289 x_api_return_status => x_return_status
290 );
291 --
292 --
293 --{ API Body
294 --
295 l_location := '0025';
296 --
297 IF p_program_unit_parameter_name IS NULL
298 OR p_tps_parameter_name IS NULL
299 THEN
300 vea_tpa_util_pvt.add_message_and_raise
301 (
302 p_error_name => 'VEA_INCOMPATIBLE_LAYER_FILE'
303 );
304 END IF;
305 --
306 l_location := '0030';
307 --
308 l_parameter_mapping_id := NULL;
309 l_program_unit_parameter_id := p_program_unit_parameter_id;
310 l_tps_parameter_id := p_tps_parameter_id;
311 l_layer_header_id := vea_layer_headers_sv.g_layer_header_id;
312 --
313 --
314 l_location := '0040';
315 --
316 l_program_unit_parameter_id := vea_parameters_sv.getId
317 (
318 p_layer_provider_code => p_program_unit_param_lp_code,
319 p_program_unit_id => vea_layer_headers_sv.g_tpa_program_unit_id,
320 p_name => p_program_unit_parameter_name
321 );
322 --
323 --
324 l_location := '0050';
325 --
326 l_tps_parameter_id := vea_parameters_sv.getId
327 (
328 p_layer_provider_code => p_tps_parameter_lp_code,
329 p_program_unit_id => vea_layer_headers_sv.g_tps_program_unit_id,
330 p_name => p_tps_parameter_name
331 );
332 --
333 --
334 l_location := '0060';
335 --
336 FOR parameter_mapping_rec IN parameter_mapping_cur
337 (
338 p_layer_provider_code => p_layer_provider_code,
339 p_parameter_mapping_id => p_id,
340 p_layer_header_id => l_layer_header_id,
341 p_tps_parameter_id => l_tps_parameter_id,
345 --{
342 p_tps_parameter_lp_code => p_tps_parameter_lp_code
343 )
344 LOOP
346 l_location := '0070';
347 --
348 l_parameter_mapping_id := parameter_mapping_rec.parameter_mapping_id;
349 --}
350 END LOOP;
351 --
352 --
353 l_location := '0080';
354 --
355 IF l_parameter_mapping_id IS NULL
356 THEN
357 --{
358 l_location := '0090';
359 --
360 --
361 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_program_unit_param_lp_code is ' || p_program_unit_param_lp_code);
362 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' vea_layer_headers_sv.g_tpa_program_unit_id is ' || vea_layer_headers_sv.g_tpa_program_unit_id);
363 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' vea_layer_headers_sv.g_program_unit_id is ' || vea_layer_headers_sv.g_program_unit_id);
364 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_program_unit_parameter_name is ' || p_program_unit_parameter_name);
365 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' l_tps_parameter_id is ' || l_tps_parameter_id);
366 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_layer_provider_code is ' || p_layer_provider_code);
367 IF p_layer_provider_code = vea_tpa_util_pvt.g_current_layer_provider_code
368 THEN
369 SELECT NVL( p_id, vea_parameter_mappings_s.NEXTVAL )
370 INTO l_parameter_mapping_id
371 FROM DUAL;
372 ELSE
373 SELECT vea_parameter_mappings_s.NEXTVAL
374 INTO l_parameter_mapping_id
375 FROM DUAL;
376 END IF;
377 --
378 --
379 --
380 l_location := '0100';
381 --
382 insert_row
383 (
384 p_layer_provider_code => p_layer_provider_code,
385 p_parameter_mapping_id => l_parameter_mapping_id,
386 p_layer_header_id => l_layer_header_id,
387 p_program_unit_parameter_id => l_program_unit_parameter_id,
388 p_program_unit_param_lp_code => p_program_unit_param_lp_code,
389 p_tps_parameter_id => l_tps_parameter_id,
390 p_tps_parameter_lp_code => p_tps_parameter_lp_code,
391 p_description => p_description
392 );
393 --}
394 ELSE
395 --{
396 l_location := '0110';
397 --
398 update_row
399 (
400 p_layer_provider_code => p_layer_provider_code,
401 p_parameter_mapping_id => l_parameter_mapping_id,
402 p_layer_header_id => l_layer_header_id,
403 p_program_unit_parameter_id => l_program_unit_parameter_id,
404 p_program_unit_param_lp_code => p_program_unit_param_lp_code,
405 p_tps_parameter_id => l_tps_parameter_id,
406 p_tps_parameter_lp_code => p_tps_parameter_lp_code,
407 p_description => p_description
408 );
409 --}
410 END IF;
411 --
412 --
413 l_location := '0120';
414 --
415 x_id := l_parameter_mapping_id;
416 --
417 --
418 --} API Body
419 --
420 --
421 -- Standard API Footer
422 --
423 l_location := '0130';
424 --
425 vea_tpa_util_pvt.api_footer
426 (
427 p_commit => p_commit,
428 x_msg_count => x_msg_count,
429 x_msg_data => x_msg_data
430 );
431 --}
432 EXCEPTION
433 --{
434 WHEN FND_API.G_EXC_ERROR
435 THEN
436 --{
437 --RAISE;
438 vea_tpa_util_pvt.handle_error
439 (
440 p_error_type => vea_tpa_util_pvt.G_ERROR,
441 p_savepoint_name => l_savepoint_name,
442 p_package_name => G_PACKAGE_NAME,
443 p_api_name => l_api_name,
444 p_location => l_location,
445 x_msg_count => x_msg_count,
446 x_msg_data => x_msg_data,
447 x_api_return_status => x_return_status
448 );
449 --}
450 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
451 THEN
452 --{
453 --RAISE;
454 vea_tpa_util_pvt.handle_error
455 (
456 p_error_type => vea_tpa_util_pvt.G_UNEXPECTED_ERROR,
457 p_savepoint_name => l_savepoint_name,
458 p_package_name => G_PACKAGE_NAME,
459 p_api_name => l_api_name,
460 p_location => l_location,
461 x_msg_count => x_msg_count,
462 x_msg_data => x_msg_data,
463 x_api_return_status => x_return_status
464 );
465 --}
466 WHEN OTHERS
467 THEN
468 --{
469 --RAISE;
470 vea_tpa_util_pvt.handle_error
471 (
472 p_error_type => vea_tpa_util_pvt.G_OTHER_ERROR,
473 p_savepoint_name => l_savepoint_name,
474 p_package_name => G_PACKAGE_NAME,
475 p_api_name => l_api_name,
476 p_location => l_location,
477 x_msg_count => x_msg_count,
478 x_msg_data => x_msg_data,
479 x_api_return_status => x_return_status
480 );
481 --}
482 --}
483 END process;
484 --}
485 END VEA_PARAMETER_MAPPINGS_SV;