[Home] [Help]
PACKAGE BODY: APPS.CCT_LKUP_PUB
Source
1 PACKAGE BODY CCT_LKUP_PUB as
2 /* $Header: cctplovb.pls 120.0 2005/06/02 09:06:04 appldev noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) :='CCT_LKUP_PUB';
5
6
7
8 PROCEDURE GET_TRUE_FALSE_LOV
9 (
10 p_server_group_id IN NUMBER , -- server group id
11 p_server_id IN NUMBER , -- server id
12 p_server_parameter_id IN NUMBER , -- server parameter id
13 p_env_lang IN VARCHAR2 , -- language
14 x_lov_count OUT NOCOPY NUMBER , -- number of lov_data returned
15 x_lov_data OUT NOCOPY IEO_STRING_VARR, -- list of lov_data returned.
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2
19 )
20 IS
21 l_api_name CONSTANT VARCHAR2(30) := 'GET_TRUE_FALSE_LOV';
22 l_api_version CONSTANT NUMBER := 1.0;
23 x_err_num NUMBER;
24 x_err_msg VARCHAR2(256);
25 l_lov_meaning IEO_STRING_VARR;
26 l_lov_code IEO_STRING_VARR;
27 i number;
28 j number;
29
30 BEGIN
31 -- Standard Start of API savepoint
32
33 SAVEPOINT GET_TRUE_FALSE_LOV_PUB;
34
35 -- Initialize API return status to success
36 x_return_status := FND_API.G_RET_STS_SUCCESS;
37 x_msg_count := 0;
38 x_msg_data := null;
39
40 x_lov_count := 0;
41 x_lov_data := IEO_STRING_VARR();
42 l_lov_meaning := IEO_STRING_VARR();
43 l_lov_code := IEO_STRING_VARR();
44
45 -- API body
46
47 SELECT lv.MEANING, lv.lookup_code BULK COLLECT INTO l_lov_meaning, l_lov_code FROM FND_LOOKUP_VALUES LV
48 WHERE LV.VIEW_APPLICATION_ID = 0
49 AND LV.SECURITY_GROUP_ID = fnd_global.lookup_security_group(LV.LOOKUP_TYPE, LV.VIEW_APPLICATION_ID)
50 AND LV.ENABLED_FLAG='Y'
51 AND LV.LANGUAGE = p_env_lang
52 AND LV.LOOKUP_TYPE = 'CCT_BOOLEAN';
53
54 --dbms_output.put_line(' l_lov_meaning => ' || l_lov_meaning(1) || ' ' || l_lov_meaning(2));
55 --dbms_output.put_line(' l_lov_code => ' || l_lov_code(1) || ' ' || l_lov_code(2) );
56 --dbms_output.put_line(' l_lov_code.count => '|| l_lov_code.count);
57
58 j := 0;
59
60 FOR i IN 1..l_lov_code.count LOOP
61 x_lov_data.extend();
62 j := j+1;
63 x_lov_data(j) := l_lov_code(i);
64 x_lov_data.extend();
65 j := j+1;
66 x_lov_data(j) := l_lov_meaning(i);
67 END LOOP;
68
69
70 x_lov_count := x_lov_data.count;
71
72 -- End of API body.
73
74 EXCEPTION
75
76 WHEN OTHERS THEN
77 ROLLBACK TO GET_TRUE_FALSE_LOV_PUB;
78 x_return_status := FND_API.G_RET_STS_ERROR ;
79 x_msg_count := 1;
80 x_err_num := SQLCODE;
81 x_err_msg := SUBSTR(SQLERRM, 1, 100);
82 x_msg_data := 'CCT_LKUP_PUB: CCT_ERROR '
83 || ' ErrorCode = ' || x_err_num
84 || ' ErrorMsg = ' || x_err_msg;
85 --dbms_output.put_line(x_msg_data);
86
87 END GET_TRUE_FALSE_LOV;
88
89
90 PROCEDURE GET_JDBC_CONN_LOV
91 (
92 p_server_group_id IN NUMBER , -- server group id
93 p_server_id IN NUMBER , -- server id
94 p_server_parameter_id IN NUMBER , -- server parameter id
95 p_env_lang IN VARCHAR2 , -- language
96 x_lov_count OUT NOCOPY NUMBER , -- number of lov_data returned
97 x_lov_data OUT NOCOPY IEO_STRING_VARR, -- list of lov_data returned.
98 x_return_status OUT NOCOPY VARCHAR2,
99 x_msg_count OUT NOCOPY NUMBER,
100 x_msg_data OUT NOCOPY VARCHAR2
101
102 )IS
103
104 l_api_name CONSTANT VARCHAR2(30) := 'GET_JDBC_CONN_LOV';
105 l_api_version CONSTANT NUMBER := 1.0;
106 x_err_num NUMBER;
107 x_err_msg VARCHAR2(256);
108
109 BEGIN
110 -- Standard Start of API savepoint
111
112 SAVEPOINT GET_JDBC_CONN_LOV_PUB;
113
114 -- Initialize API return status to success
115 x_return_status := FND_API.G_RET_STS_SUCCESS;
116 x_msg_count := 0;
117 x_msg_data := null;
118 -- API body
119 x_lov_count := 0;
120 x_lov_data := IEO_STRING_VARR();
121
122
123
124 FOR i IN 1..15 LOOP
125 x_lov_data.extend;
126 x_lov_data(i) := i;
127 END LOOP;
128
129 x_lov_count := x_lov_data.count;
130
131 -- End of API body.
132
133 EXCEPTION
134
135 WHEN OTHERS THEN
136 ROLLBACK TO GET_JDBC_CONN_LOV_PUB;
137 x_return_status := FND_API.G_RET_STS_ERROR ;
138 x_msg_count := 1;
139 x_err_num := SQLCODE;
140 x_err_msg := SUBSTR(SQLERRM, 1, 100);
141 x_msg_data := 'CCT_LKUP_PUB: CCT_ERROR '
142 || ' ErrorCode = ' || x_err_num
143 || ' ErrorMsg = ' || x_err_msg;
144 --dbms_output.put_line(x_msg_data);
145
146 END GET_JDBC_CONN_LOV;
147
148
149 PROCEDURE GET_TRACE_LOV
150 (
151 p_server_group_id IN NUMBER , -- server group id
152 p_server_id IN NUMBER , -- server id
153 p_server_parameter_id IN NUMBER , -- server parameter id
154 p_env_lang IN VARCHAR2 , -- language
155 x_lov_count OUT NOCOPY NUMBER , -- number of lov_data returned
156 x_lov_data OUT NOCOPY IEO_STRING_VARR, -- list of lov_data returned.
157 x_return_status OUT NOCOPY VARCHAR2,
158 x_msg_count OUT NOCOPY NUMBER,
159 x_msg_data OUT NOCOPY VARCHAR2
160
161 )
162 IS
163
164 l_api_name CONSTANT VARCHAR2(30) := 'GET_TRACE_LOV';
165 l_api_version CONSTANT NUMBER := 1.0;
166 x_err_num NUMBER;
167 x_err_msg VARCHAR2(256);
168 l_lov_meaning IEO_STRING_VARR;
169 l_lov_code IEO_STRING_VARR;
170 i number;
171 j number;
172 BEGIN
173 -- Standard Start of API savepoint
174
175 SAVEPOINT GET_TRACE_LOV_PUB;
176
177 -- Initialize API return status to success
178 x_return_status := FND_API.G_RET_STS_SUCCESS;
179 x_msg_count := 0;
180 x_msg_data := null;
181
182 x_lov_count := 0;
183 x_lov_data := IEO_STRING_VARR();
184 l_lov_meaning := IEO_STRING_VARR();
185 l_lov_code := IEO_STRING_VARR();
186
187 -- API body
188
189 SELECT MEANING, lookup_code BULK COLLECT INTO l_lov_meaning, l_lov_code FROM FND_LOOKUP_VALUES LV
190 WHERE LV.VIEW_APPLICATION_ID = 0
191 AND LV.SECURITY_GROUP_ID = fnd_global.lookup_security_group(LV.LOOKUP_TYPE, LV.VIEW_APPLICATION_ID)
192 AND LV.ENABLED_FLAG='Y'
193 AND LV.LANGUAGE = p_env_lang
194 AND LV.LOOKUP_TYPE = 'CCT_TRACE_LEVELS';
195
196
197 j := 0;
198
199 FOR i IN 1..l_lov_code.count LOOP
200 x_lov_data.extend();
201 j := j+1;
202 x_lov_data(j) := l_lov_code(i);
203 x_lov_data.extend();
204 j := j+1;
205 x_lov_data(j) := l_lov_meaning(i);
206 END LOOP;
207
208
209 x_lov_count := x_lov_data.count;
210
211 -- End of API body.
212
213 EXCEPTION
214
215 WHEN OTHERS THEN
216 ROLLBACK TO GET_TRACE_LOV_PUB;
217 x_return_status := FND_API.G_RET_STS_ERROR ;
218 x_msg_count := 1;
219 x_err_num := SQLCODE;
220 x_err_msg := SUBSTR(SQLERRM, 1, 100);
221 x_msg_data := 'CCT_LKUP_PUB: CCT_ERROR '
222 || ' ErrorCode = ' || x_err_num
223 || ' ErrorMsg = ' || x_err_msg;
224 --dbms_output.put_line(x_msg_data);
225
226 END GET_TRACE_LOV;
227
228 PROCEDURE GET_TEST_TYPE_LOV
229 (
230 p_server_group_id IN NUMBER , -- server group id
231 p_server_id IN NUMBER , -- server id
232 p_server_parameter_id IN NUMBER , -- server parameter id
233 p_env_lang IN VARCHAR2 , -- language
234 x_lov_count OUT NOCOPY NUMBER , -- number of lov_data returned
235 x_lov_data OUT NOCOPY IEO_STRING_VARR, -- list of lov_data returned.
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_msg_count OUT NOCOPY NUMBER,
238 x_msg_data OUT NOCOPY VARCHAR2
239
240 )
241 IS
242
243 l_api_name CONSTANT VARCHAR2(30) := 'GET_TEST_TYPE_LOV';
244 l_api_version CONSTANT NUMBER := 1.0;
245 x_err_num NUMBER;
246 x_err_msg VARCHAR2(256);
247 l_lov_meaning IEO_STRING_VARR;
248 l_lov_code IEO_STRING_VARR;
249 i number;
250 j number;
251 BEGIN
252 -- Standard Start of API savepoint
253
254 SAVEPOINT GET_TEST_TYPE_LOV_PUB;
255
256 -- Initialize API return status to success
257 x_return_status := FND_API.G_RET_STS_SUCCESS;
258 x_msg_count := 0;
259 x_msg_data := null;
260
261 x_lov_count := 0;
262 x_lov_data := IEO_STRING_VARR();
263 l_lov_meaning := IEO_STRING_VARR();
264 l_lov_code := IEO_STRING_VARR();
265
266 -- API body
267
268 SELECT MEANING, lookup_code BULK COLLECT INTO l_lov_meaning, l_lov_code FROM FND_LOOKUP_VALUES LV
269 WHERE LV.VIEW_APPLICATION_ID = 0
270 AND LV.SECURITY_GROUP_ID = fnd_global.lookup_security_group(LV.LOOKUP_TYPE, LV.VIEW_APPLICATION_ID)
271 AND LV.ENABLED_FLAG='Y'
272 AND LV.LANGUAGE = p_env_lang
273 AND LV.LOOKUP_TYPE = 'CCT_TEST_TYPES';
274
275
276 j := 0;
277
278 FOR i IN 1..l_lov_code.count LOOP
279 x_lov_data.extend();
280 j := j+1;
281 x_lov_data(j) := l_lov_code(i);
282 x_lov_data.extend();
283 j := j+1;
284 x_lov_data(j) := l_lov_meaning(i);
285 END LOOP;
286
287
288 x_lov_count := x_lov_data.count;
289
290 -- End of API body.
291
292 EXCEPTION
293
294 WHEN OTHERS THEN
295 ROLLBACK TO GET_TRACE_LOV_PUB;
296 x_return_status := FND_API.G_RET_STS_ERROR ;
297 x_msg_count := 1;
298 x_err_num := SQLCODE;
299 x_err_msg := SUBSTR(SQLERRM, 1, 100);
300 x_msg_data := 'CCT_LKUP_PUB: CCT_ERROR '
301 || ' ErrorCode = ' || x_err_num
302 || ' ErrorMsg = ' || x_err_msg;
303 --dbms_output.put_line(x_msg_data);
304
305 END GET_TEST_TYPE_LOV;
306
307
308 PROCEDURE GET_MW_LOV
309 (
310 p_server_group_id IN NUMBER , -- server group id
311 p_server_id IN NUMBER , -- server id
312 p_server_parameter_id IN NUMBER , -- server parameter id
313 p_env_lang IN VARCHAR2 , -- language
314 x_lov_count OUT NOCOPY NUMBER , -- number of lov_data returned
315 x_lov_data OUT NOCOPY IEO_STRING_VARR, -- list of lov_data returned.
316 x_return_status OUT NOCOPY VARCHAR2,
317 x_msg_count OUT NOCOPY NUMBER,
318 x_msg_data OUT NOCOPY VARCHAR2
319
320 )
321 IS
322
323 l_api_name CONSTANT VARCHAR2(30) := 'GET_MW_LOV';
324 l_api_version CONSTANT NUMBER := 1.0;
325 x_err_num NUMBER;
326 x_err_msg VARCHAR2(256);
327
328 BEGIN
329 -- Standard Start of API savepoint
330
331 SAVEPOINT GET_MW_LOV_PUB;
332
333 -- Initialize API return status to success
334 x_return_status := FND_API.G_RET_STS_SUCCESS;
335 x_msg_count := 0;
336 x_msg_data := null;
337 -- API body
338 select config_name bulk collect into x_lov_data
339 from CCT_MIDDLEWARES mw
340 where mw.server_group_id = p_server_group_id
341 AND mw.f_deletedflag is NULL;
342 x_lov_count := x_lov_data.count;
343 -- End of API body.
344
345 EXCEPTION
346
347 WHEN OTHERS THEN
348 ROLLBACK TO GET_MW_LOV_PUB;
349 x_return_status := FND_API.G_RET_STS_ERROR ;
350 x_msg_count := 1;
351 x_err_num := SQLCODE;
352 x_err_msg := SUBSTR(SQLERRM, 1, 100);
353 x_msg_data := 'CCT_LKUP_PUB: CCT_ERROR '
354 || ' ErrorCode = ' || x_err_num
355 || ' ErrorMsg = ' || x_err_msg;
356 --dbms_output.put_line(x_msg_data);
357
358 END GET_MW_LOV;
359
360
361
362
363 PROCEDURE GET_ROUTE_POINT_LOV
364 (
365 p_server_group_id IN NUMBER , -- server group id
366 p_server_id IN NUMBER , -- server id
367 p_server_parameter_id IN NUMBER , -- server parameter id
368 p_env_lang IN VARCHAR2 , -- language
369 x_lov_count OUT NOCOPY NUMBER , -- number of lov_data returned
370 x_lov_data OUT NOCOPY IEO_STRING_VARR, -- list of lov_data returned.
371 x_return_status OUT NOCOPY VARCHAR2,
372 x_msg_count OUT NOCOPY NUMBER,
373 x_msg_data OUT NOCOPY VARCHAR2
374
375 )
376 IS
377
378 l_api_name CONSTANT VARCHAR2(30) := 'GET_ROUTE_POINT_LOV';
379 l_api_version CONSTANT NUMBER := 1.0;
380 x_err_num NUMBER;
381 x_err_msg VARCHAR2(256);
382 j number;
383 i number;
384
385 BEGIN
386 -- Standard Start of API savepoint
387
388 SAVEPOINT GET_ROUTE_POINT_LOV_PUB;
389
390 -- Initialize API return status to success
391 x_return_status := FND_API.G_RET_STS_SUCCESS;
392 x_msg_count := 0;
393 x_msg_data := null;
394 -- API body
395 select config_name || ': ' || route_point_number bulk collect into x_lov_data
396 from CCT_MIDDLEWARES mw, CCT_MW_ROUTE_POINTS rp
397 where mw.middleware_id = rp.middleware_id
398 AND mw.server_group_id = p_server_group_id
399 AND rp.f_deletedflag is NULL;
400
401 x_lov_data.extend();
402 FOR i IN REVERSE 2..x_lov_data.count LOOP
403 x_lov_data(i) := x_lov_data(i-1);
404 END LOOP;
405 x_lov_data(1) := '';
406
407 x_lov_count := x_lov_data.count;
408 -- End of API body.
409
410 EXCEPTION
411
412 WHEN OTHERS THEN
413 ROLLBACK TO GET_ROUTE_POINT_LOV_PUB;
414 x_return_status := FND_API.G_RET_STS_ERROR ;
415 x_msg_count := 1;
416 x_err_num := SQLCODE;
417 x_err_msg := SUBSTR(SQLERRM, 1, 100);
418 x_msg_data := 'CCT_LKUP_PUB: CCT_ERROR '
419 || ' ErrorCode = ' || x_err_num
420 || ' ErrorMsg = ' || x_err_msg;
421 --dbms_output.put_line(x_msg_data);
422
423 END GET_ROUTE_POINT_LOV;
424
425
426 END CCT_LKUP_PUB;
427