[Home] [Help]
PACKAGE BODY: APPS.VEA_LAYER_LICENSES_SV
Source
1 PACKAGE BODY VEA_LAYER_LICENSES_SV as
2 /* $Header: VEAVALLB.pls 115.9 2004/07/27 00:08:01 rvishnuv ship $ */
3 --{
4 /*====================== vea_layer_licenses_sv =========================*/
5 /*========================================================================
6 PURPOSE: Table handler package for table VEA_LAYER_LICENSES
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_LAYER_LICENSES_SV';
18 --
19 --
20 /*========================================================================
21
22 PROCEDURE NAME: insert_row
23
24 PURPOSE: Inserts a record into VEA_LAYER_LICENSES table
25
26 ========================================================================*/
27 PROCEDURE
28 insert_row
29 (
30 p_layer_provider_code IN vea_layer_licenses.layer_provider_code%TYPE,
31 p_customer_name IN vea_layer_licenses.customer_name%TYPE,
32 p_description IN vea_layer_licenses.description%TYPE,
33 p_tp_layer_id IN vea_layer_licenses.tp_layer_id%TYPE
34 )
35 IS
36 --{
37 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
38 l_location VARCHAR2(32767);
39 --
40 --
41 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
42 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
43 --}
44 BEGIN
45 --{
46 l_location := '0010';
47 --
48 INSERT INTO vea_layer_licenses
49 (
50 layer_provider_code,
51 customer_name,
52 description,
53 tp_layer_id,
54 created_by, creation_date,
55 last_updated_by, last_update_date,
56 last_update_login
57 )
58 VALUES
59 (
60 p_layer_provider_code,
61 p_customer_name,
62 p_description,
63 p_tp_layer_id,
64 l_user_id, SYSDATE,
65 l_user_id, SYSDATE,
66 l_login_id
67 );
68 --}
69 EXCEPTION
70 --{
71 WHEN OTHERS
72 THEN
73 --{
74 vea_tpa_util_pvt.add_exc_message_and_raise
75 (
76 p_package_name => G_PACKAGE_NAME,
77 p_api_name => l_api_name,
78 p_location => l_location
79 );
80 --}
81 --}
82 END insert_row;
83 --
84 --
85 /*========================================================================
86
87 PROCEDURE NAME: update_row
88
89 PURPOSE: Updates a record into VEA_LAYER_LICENSES table
90
91 ========================================================================*/
92 PROCEDURE
93 update_row
94 (
95 p_layer_provider_code IN vea_layer_licenses.layer_provider_code%TYPE,
96 p_customer_name IN vea_layer_licenses.customer_name%TYPE,
97 p_description IN vea_layer_licenses.description%TYPE,
98 p_tp_layer_id IN vea_layer_licenses.tp_layer_id%TYPE
99 )
100 IS
101 --{
102 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
103 l_location VARCHAR2(32767);
104 --
105 --
106 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
107 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
108 --}
109 BEGIN
110 --{
111 l_location := '0010';
112 --
113 UPDATE vea_layer_licenses
114 SET description = p_description,
115 last_updated_by = l_user_id,
116 last_update_date = SYSDATE,
117 last_update_login = l_login_id
118 WHERE layer_provider_code = p_layer_provider_code
119 AND tp_layer_id = p_tp_layer_id
120 AND customer_name = p_customer_name;
121 --}
122 EXCEPTION
123 --{
124 WHEN OTHERS
125 THEN
126 --{
127 vea_tpa_util_pvt.add_exc_message_and_raise
128 (
129 p_package_name => G_PACKAGE_NAME,
130 p_api_name => l_api_name,
131 p_location => l_location
132 );
133 --}
134 --}
135 END update_row;
136 --
137 --
138 /*========================================================================
139
140 PROCEDURE NAME: isLayerLicensed
141
142 PURPOSE: Returns True, if layer is licensed to the customer.
143
144 ========================================================================*/
145 FUNCTION
146 isLicensed
147 (
148 p_layer_provider_code IN vea_layer_licenses.layer_provider_code%TYPE,
149 p_tp_layer_id IN vea_layer_licenses.tp_layer_id%TYPE
150 )
151 RETURN BOOLEAN
152 IS
153 --{
154 l_api_name CONSTANT VARCHAR2(30) := 'isLicensed';
155 l_location VARCHAR2(32767);
156 --
157 --
158 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
159 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
160 --
161 --
162 CURSOR layer_license_cur
163 (
164 p_layer_provider_code IN vea_layer_licenses.layer_provider_code%TYPE,
165 p_tp_layer_id IN vea_layer_licenses.tp_layer_id%TYPE
166 )
167 IS
168 SELECT 'x'
169 FROM vea_layer_licenses
170 WHERE layer_provider_code = p_layer_provider_code
171 AND tp_layer_id = p_tp_layer_id;
172 --
173 --
174 l_count NUMBER;
175 --}
176 BEGIN
177 --{
178 l_location := '0010';
179 --
180 IF p_tp_layer_id IS NULL
181 THEN
182 RETURN(TRUE);
183 END IF;
184 --
185 --
186 l_location := '0015';
187 --
188 IF NOT( vea_tpa_util_pvt.isLayerMergeOn )
189 THEN
190 RETURN(TRUE);
191 END IF;
192 --
193 --
194 l_location := '0016';
195 --
196 IF vea_tpa_util_pvt.get_curr_layer_provider_code = p_layer_provider_code
197 THEN
198 RETURN(TRUE);
199 END IF;
200 --
201 --
202 l_location := '0017';
203 --
204 --IF vea_tpa_util_pvt.get_curr_layer_provider_code like 'CUST%'
205 --THEN
206 --{
207 l_location := '0020';
208 --
209 l_count := 0;
210 --
211 --
212 l_location := '0030';
213 --
214 FOR layer_license_rec IN layer_license_cur
215 (
216 p_layer_provider_code => p_layer_provider_code,
217 p_tp_layer_id => p_tp_layer_id
218 )
219 LOOP
220 --{
221 l_location := '0040';
222 --
223 l_count := l_count + 1;
224 --}
225 END LOOP;
226 --
227 --
228 l_location := '0050';
229 --
230 IF l_count = 0
231 THEN
232 --{
233 l_location := '0060';
234 --
235 RETURN(FALSE);
236 --}
237 END IF;
238 --}
239 --END IF;
240 --
241 --
242 l_location := '0070';
243 --
244 RETURN(TRUE);
245 --}
246 EXCEPTION
247 --{
248 WHEN OTHERS
249 THEN
250 --{
251 vea_tpa_util_pvt.add_exc_message_and_raise
252 (
253 p_package_name => G_PACKAGE_NAME,
254 p_api_name => l_api_name,
255 p_location => l_location
256 );
257 --}
258 --}
259 END isLicensed;
260 --
261 --
262 /*========================================================================
263
264 PROCEDURE NAME: delete_rows
265
266 PURPOSE: Deletes all licenses for the specified TP Layer.
267
268 ========================================================================*/
269 PROCEDURE
270 delete_rows
271 (
272 p_layer_provider_code IN vea_layer_licenses.layer_provider_code%TYPE,
273 p_tp_layer_id IN vea_layer_licenses.tp_layer_id%TYPE
274 )
275 IS
276 --{
277 l_api_name CONSTANT VARCHAR2(30) := 'delete_rows';
278 l_location VARCHAR2(32767);
279 --
280 --
281 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
282 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
283 --}
284 BEGIN
285 --{
286 l_location := '0010';
287 --
288 DELETE vea_layer_licenses
289 WHERE layer_provider_code = p_layer_provider_code
290 AND tp_layer_id = p_tp_layer_id;
291 --}
292 EXCEPTION
293 --{
294 WHEN OTHERS
295 THEN
296 --{
297 vea_tpa_util_pvt.add_exc_message_and_raise
298 (
299 p_package_name => G_PACKAGE_NAME,
300 p_api_name => l_api_name,
301 p_location => l_location
302 );
303 --}
304 --}
305 END delete_rows;
306 --
307 --
308 /*========================================================================
309
310 PROCEDURE NAME: process
311
312 PURPOSE: Table hadndler API for VEA_LAYER_LICENSES table.
313
314 It inserts/updates a record in VEA_LAYER_LICENSES table.
315
316 ========================================================================*/
317 PROCEDURE
318 process
319 (
320 p_api_version IN NUMBER,
321 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
322 p_commit IN VARCHAR2 := FND_API.G_FALSE,
323 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
324 x_return_status OUT NOCOPY VARCHAR2,
325 x_msg_count OUT NOCOPY NUMBER,
326 x_msg_data OUT NOCOPY VARCHAR2,
327 p_layer_provider_code IN vea_layer_licenses.layer_provider_code%TYPE,
328 p_customer_name IN vea_layer_licenses.customer_name%TYPE,
329 p_description IN vea_layer_licenses.description%TYPE,
330 p_tp_layer_id IN vea_layer_licenses.tp_layer_id%TYPE
331 )
332 IS
333 --{
334 l_api_name CONSTANT VARCHAR2(30) := 'process';
335 l_api_version CONSTANT NUMBER := 1.0;
336 l_api_type CONSTANT VARCHAR2(3) := vea_tpa_util_pvt.G_PUBLIC_API;
337 --
338 --
339 l_location VARCHAR2(32767);
340 l_savepoint_name VARCHAR2(30);
341 l_id vea_layers.layer_id%TYPE;
342 l_tp_layer_id vea_tp_layers.tp_layer_id%TYPE;
343 --
344 --
345 CURSOR layer_cur
346 (
347 p_layer_provider_code IN vea_layer_licenses.layer_provider_code%TYPE,
348 p_customer_name IN vea_layer_licenses.customer_name%TYPE,
349 p_tp_layer_id IN vea_layer_licenses.tp_layer_id%TYPE
350 )
351 IS
352 SELECT 1 id
353 FROM vea_layer_licenses
354 WHERE layer_provider_code = p_layer_provider_code
355 AND tp_layer_id = p_tp_layer_id
356 AND customer_name = p_customer_name;
357 --
358 --
359 --}
360 BEGIN
361 --{
362 l_location := '0010';
363 --
364 IF NOT( vea_tpa_util_pvt.is_vea_installed() )
365 THEN
366 RETURN;
367 END IF;
368 --
369 --
370 -- Standard API Header
371 --
372 l_location := '0020';
373 --
374 vea_tpa_util_pvt.api_header
375 (
376 p_package_name => G_PACKAGE_NAME,
377 p_api_name => l_api_name,
378 p_api_type => l_api_type,
379 p_api_current_version => l_api_version,
380 p_api_caller_version => p_api_version,
381 p_init_msg_list => p_init_msg_list,
382 x_savepoint_name => l_savepoint_name,
383 x_api_return_status => x_return_status
384 );
385 --
386 --
387 --{ API Body
388 --
389 --
390 l_location := '0025';
391 --
392 IF vea_tpa_util_pvt.get_curr_customer_name() = p_customer_name
393 THEN
394 --{
395 l_location := '0030';
396 --
397 l_id := NULL;
398 --
399 IF (VEA_TPA_UTIL_PVT.isLayerMergeOn) THEN
400 --{
401 l_tp_layer_id := nvl(vea_tp_layers_sv.g_tp_layer_id,p_tp_layer_id);
402 --}
403 ELSE
404 --{
405 l_tp_layer_id := p_tp_layer_id;
406 --}
407 END IF;
408 --
409 --
410 --
411 --
412 l_location := '0040';
413 --
414 FOR layer_rec IN layer_cur
415 (
416 p_layer_provider_code => p_layer_provider_code,
417 p_customer_name => p_customer_name,
418 p_tp_layer_id => l_tp_layer_id
419 )
420 LOOP
421 --{
422 l_location := '0050';
423 --
424 l_id := layer_rec.id;
425 --}
426 END LOOP;
427 --
428 --
429 l_location := '0060';
430 --
431 IF l_id IS NULL
432 THEN
433 --{
434 l_location := '0070';
435 --
436 insert_row
437 (
438 p_layer_provider_code => p_layer_provider_code,
439 p_customer_name => p_customer_name,
440 p_description => p_description,
441 p_tp_layer_id => l_tp_layer_id
442 );
443 --}
444 ELSE
445 --{
446 l_location := '0080';
447 --
448 update_row
449 (
450 p_layer_provider_code => p_layer_provider_code,
451 p_customer_name => p_customer_name,
452 p_description => p_description,
453 p_tp_layer_id => l_tp_layer_id
454 );
455 --}
456 END IF;
457 --}
458 END IF;
459 --
460 --
461 --} API Body
462 --
463 --
464 -- Standard API Footer
465 --
466 l_location := '0090';
467 --
468 vea_tpa_util_pvt.api_footer
469 (
470 p_commit => p_commit,
471 x_msg_count => x_msg_count,
472 x_msg_data => x_msg_data
473 );
474 --}
475 EXCEPTION
476 --{
477 WHEN FND_API.G_EXC_ERROR
478 THEN
479 --{
480 --RAISE;
481 vea_tpa_util_pvt.handle_error
482 (
483 p_error_type => vea_tpa_util_pvt.G_ERROR,
484 p_savepoint_name => l_savepoint_name,
485 p_package_name => G_PACKAGE_NAME,
486 p_api_name => l_api_name,
487 p_location => l_location,
488 x_msg_count => x_msg_count,
489 x_msg_data => x_msg_data,
490 x_api_return_status => x_return_status
491 );
492 --}
493 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
494 THEN
495 --{
496 --RAISE;
497 vea_tpa_util_pvt.handle_error
498 (
499 p_error_type => vea_tpa_util_pvt.G_UNEXPECTED_ERROR,
500 p_savepoint_name => l_savepoint_name,
501 p_package_name => G_PACKAGE_NAME,
502 p_api_name => l_api_name,
503 p_location => l_location,
504 x_msg_count => x_msg_count,
505 x_msg_data => x_msg_data,
506 x_api_return_status => x_return_status
507 );
508 --}
509 WHEN OTHERS
510 THEN
511 --{
512 --RAISE;
513 vea_tpa_util_pvt.handle_error
514 (
515 p_error_type => vea_tpa_util_pvt.G_OTHER_ERROR,
516 p_savepoint_name => l_savepoint_name,
517 p_package_name => G_PACKAGE_NAME,
518 p_api_name => l_api_name,
519 p_location => l_location,
520 x_msg_count => x_msg_count,
521 x_msg_data => x_msg_data,
522 x_api_return_status => x_return_status
523 );
524 --}
525 --}
526 END process;
527 --}
528 END VEA_LAYER_LICENSES_SV;