[Home] [Help]
PACKAGE BODY: APPS.OKC_PARAMETERS_PUB
Source
1 Package body OKC_PARAMETERS_PUB AS
2 /* $Header: OKCPPRMB.pls 120.2 2006/02/28 14:43:00 smallya noship $ */
3 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4
5 TYPE Params_Tbl_Type IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
6
7 G_Params_tbl Params_Tbl_Type;
8 G_Params_count number := 0;
9
10 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
11 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_PARAMETERS_PUB';
12 G_SQL_ID number;
13
14 -- Start of comments
15 --
16 -- Procedure Name : add_language
17 -- Description :
18 -- Business Rules :
19 -- Parameters :
20 -- Version : 1.0
21 -- End of comments
22
23 procedure add_language is
24 begin
25 OKC_PRM_PVT.add_language;
26 end add_language;
27
28 -- Start of comments
29 --
30 -- Procedure Name : create_parameter
31 -- Description :
32 -- Business Rules :
33 -- Parameters :
34 -- Version : 1.0
35 -- End of comments
36 procedure create_parameter(p_api_version IN NUMBER,
37 p_init_msg_list IN VARCHAR2 ,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2,
41 p_prmv_rec IN prmv_rec_type,
42 x_prmv_rec OUT NOCOPY prmv_rec_type) is
43 l_api_name CONSTANT VARCHAR2(30) := 'create_parameter';
44 l_api_version CONSTANT NUMBER := 1;
45 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
46 begin
47 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
48 G_PKG_NAME,
49 p_init_msg_list,
50 l_api_version,
51 p_api_version,
52 '_PUB',
53 x_return_status);
54 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
55 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
56 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
57 RAISE OKC_API.G_EXCEPTION_ERROR;
58 END IF;
59 OKC_PRM_PVT.insert_row(p_api_version,
60 p_init_msg_list,
61 x_return_status,
62 x_msg_count,
63 x_msg_data,
64 p_prmv_rec,
65 x_prmv_rec);
66 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
67 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
68 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
69 RAISE OKC_API.G_EXCEPTION_ERROR;
70 END IF;
71 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
72 EXCEPTION
73 WHEN OKC_API.G_EXCEPTION_ERROR THEN
74 x_return_status := OKC_API.HANDLE_EXCEPTIONS
75 (substr(l_api_name,1,26),
76 G_PKG_NAME,
77 'OKC_API.G_RET_STS_ERROR',
78 x_msg_count,
79 x_msg_data,
80 '_PUB');
81 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
82 x_return_status := OKC_API.HANDLE_EXCEPTIONS
83 (substr(l_api_name,1,26),
84 G_PKG_NAME,
85 'OKC_API.G_RET_STS_UNEXP_ERROR',
86 x_msg_count,
87 x_msg_data,
88 '_PUB');
89 WHEN OTHERS THEN
90 x_return_status := OKC_API.HANDLE_EXCEPTIONS
91 (substr(l_api_name,1,26),
92 G_PKG_NAME,
93 'OTHERS',
94 x_msg_count,
95 x_msg_data,
96 '_PUB');
97 end create_parameter;
98
99 procedure create_parameter(p_api_version IN NUMBER,
100 p_init_msg_list IN VARCHAR2 ,
101 x_return_status OUT NOCOPY VARCHAR2,
102 x_msg_count OUT NOCOPY NUMBER,
103 x_msg_data OUT NOCOPY VARCHAR2,
104 p_prmv_tbl IN prmv_tbl_type,
105 x_prmv_tbl OUT NOCOPY prmv_tbl_type) is
106 i NUMBER;
107 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
108 begin
109 OKC_API.init_msg_list(p_init_msg_list);
110 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
111 if (p_prmv_tbl.COUNT>0) then
112 i := p_prmv_tbl.FIRST;
113 LOOP
114 create_parameter(p_api_version=>p_api_version,
115 p_init_msg_list=>OKC_API.G_FALSE,
116 x_return_status=>l_return_status,
117 x_msg_count=>x_msg_count,
118 x_msg_data=>x_msg_data,
119 p_prmv_rec=>p_prmv_tbl(i),
120 x_prmv_rec=>x_prmv_tbl(i));
121 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
122 x_return_status := l_return_status;
123 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
124 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
125 x_return_status := l_return_status;
126 end if;
127 EXIT WHEN (i=p_prmv_tbl.LAST);
128 i := p_prmv_tbl.NEXT(i);
129 END LOOP;
130 end if;
131 exception
132 when others then NULL;
133 end create_parameter;
134
135 procedure update_parameter(p_api_version IN NUMBER,
136 p_init_msg_list IN VARCHAR2 ,
137 x_return_status OUT NOCOPY VARCHAR2,
138 x_msg_count OUT NOCOPY NUMBER,
139 x_msg_data OUT NOCOPY VARCHAR2,
140 p_prmv_rec IN prmv_rec_type,
141 x_prmv_rec OUT NOCOPY prmv_rec_type) is
142 l_api_name CONSTANT VARCHAR2(30) := 'update_parameter';
143 l_api_version CONSTANT NUMBER := 1;
144 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
145 begin
146 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
147 G_PKG_NAME,
148 p_init_msg_list,
149 l_api_version,
150 p_api_version,
151 '_PUB',
152 x_return_status);
153 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
154 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
155 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
156 RAISE OKC_API.G_EXCEPTION_ERROR;
157 END IF;
158 OKC_PRM_PVT.update_row(p_api_version,
159 p_init_msg_list,
160 x_return_status,
161 x_msg_count,
162 x_msg_data,
163 p_prmv_rec,
164 x_prmv_rec);
165 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
166 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
167 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
168 RAISE OKC_API.G_EXCEPTION_ERROR;
169 END IF;
170 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
171 EXCEPTION
172 WHEN OKC_API.G_EXCEPTION_ERROR THEN
173 x_return_status := OKC_API.HANDLE_EXCEPTIONS
174 (substr(l_api_name,1,26),
175 G_PKG_NAME,
176 'OKC_API.G_RET_STS_ERROR',
177 x_msg_count,
178 x_msg_data,
179 '_PUB');
180 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
181 x_return_status := OKC_API.HANDLE_EXCEPTIONS
182 (substr(l_api_name,1,26),
183 G_PKG_NAME,
184 'OKC_API.G_RET_STS_UNEXP_ERROR',
185 x_msg_count,
186 x_msg_data,
187 '_PUB');
188 WHEN OTHERS THEN
189 x_return_status := OKC_API.HANDLE_EXCEPTIONS
190 (substr(l_api_name,1,26),
191 G_PKG_NAME,
192 'OTHERS',
193 x_msg_count,
194 x_msg_data,
195 '_PUB');
196 end update_parameter;
197
198 procedure update_parameter(p_api_version IN NUMBER,
199 p_init_msg_list IN VARCHAR2 ,
200 x_return_status OUT NOCOPY VARCHAR2,
201 x_msg_count OUT NOCOPY NUMBER,
202 x_msg_data OUT NOCOPY VARCHAR2,
203 p_prmv_tbl IN prmv_tbl_type,
204 x_prmv_tbl OUT NOCOPY prmv_tbl_type) is
205 i NUMBER;
206 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
207 begin
208 OKC_API.init_msg_list(p_init_msg_list);
209 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
210 if (p_prmv_tbl.COUNT>0) then
211 i := p_prmv_tbl.FIRST;
212 LOOP
213 update_parameter(p_api_version=>p_api_version,
214 p_init_msg_list=>OKC_API.G_FALSE,
215 x_return_status=>l_return_status,
216 x_msg_count=>x_msg_count,
217 x_msg_data=>x_msg_data,
218 p_prmv_rec=>p_prmv_tbl(i),
219 x_prmv_rec=>x_prmv_tbl(i));
220 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
221 x_return_status := l_return_status;
222 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
223 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
224 x_return_status := l_return_status;
225 end if;
226 EXIT WHEN (i=p_prmv_tbl.LAST);
227 i := p_prmv_tbl.NEXT(i);
228 END LOOP;
229 end if;
230 exception
231 when others then NULL;
232 end update_parameter;
233
234 procedure delete_parameter(p_api_version IN NUMBER,
235 p_init_msg_list IN VARCHAR2 ,
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_msg_count OUT NOCOPY NUMBER,
238 x_msg_data OUT NOCOPY VARCHAR2,
239 p_prmv_rec IN prmv_rec_type) is
240 l_api_name CONSTANT VARCHAR2(30) := 'delete_parameter';
241 l_api_version CONSTANT NUMBER := 1;
242 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
243 begin
244 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
245 G_PKG_NAME,
246 p_init_msg_list,
247 l_api_version,
248 p_api_version,
249 '_PUB',
250 x_return_status);
251 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
252 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
253 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
254 RAISE OKC_API.G_EXCEPTION_ERROR;
255 END IF;
256 OKC_PRM_PVT.delete_row(p_api_version,
257 p_init_msg_list,
258 x_return_status,
259 x_msg_count,
260 x_msg_data,
261 p_prmv_rec);
262 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
263 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
264 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
265 RAISE OKC_API.G_EXCEPTION_ERROR;
266 END IF;
267 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
268 EXCEPTION
269 WHEN OKC_API.G_EXCEPTION_ERROR THEN
270 x_return_status := OKC_API.HANDLE_EXCEPTIONS
271 (substr(l_api_name,1,26),
272 G_PKG_NAME,
273 'OKC_API.G_RET_STS_ERROR',
274 x_msg_count,
275 x_msg_data,
276 '_PUB');
277 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
278 x_return_status := OKC_API.HANDLE_EXCEPTIONS
279 (substr(l_api_name,1,26),
280 G_PKG_NAME,
281 'OKC_API.G_RET_STS_UNEXP_ERROR',
282 x_msg_count,
283 x_msg_data,
284 '_PUB');
285 WHEN OTHERS THEN
286 x_return_status := OKC_API.HANDLE_EXCEPTIONS
287 (substr(l_api_name,1,26),
288 G_PKG_NAME,
289 'OTHERS',
290 x_msg_count,
291 x_msg_data,
292 '_PUB');
293 end delete_parameter;
294
295 procedure delete_parameter(p_api_version IN NUMBER,
296 p_init_msg_list IN VARCHAR2 ,
297 x_return_status OUT NOCOPY VARCHAR2,
298 x_msg_count OUT NOCOPY NUMBER,
299 x_msg_data OUT NOCOPY VARCHAR2,
300 p_prmv_tbl IN prmv_tbl_type) is
301 i NUMBER;
302 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
303 begin
304 OKC_API.init_msg_list(p_init_msg_list);
305 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
306 if (p_prmv_tbl.COUNT>0) then
307 i := p_prmv_tbl.FIRST;
308 LOOP
309 delete_parameter(p_api_version=>p_api_version,
310 p_init_msg_list=>OKC_API.G_FALSE,
311 x_return_status=>l_return_status,
312 x_msg_count=>x_msg_count,
313 x_msg_data=>x_msg_data,
314 p_prmv_rec=>p_prmv_tbl(i));
315 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
316 x_return_status := l_return_status;
317 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
318 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
319 x_return_status := l_return_status;
320 end if;
321 EXIT WHEN (i=p_prmv_tbl.LAST);
322 i := p_prmv_tbl.NEXT(i);
323 END LOOP;
324 end if;
325 exception
326 when others then NULL;
327 end delete_parameter;
328
329 procedure lock_parameter(p_api_version IN NUMBER,
330 p_init_msg_list IN VARCHAR2 ,
331 x_return_status OUT NOCOPY VARCHAR2,
332 x_msg_count OUT NOCOPY NUMBER,
333 x_msg_data OUT NOCOPY VARCHAR2,
334 p_prmv_rec IN prmv_rec_type) is
335 l_api_name CONSTANT VARCHAR2(30) := 'lock_parameter';
336 l_api_version CONSTANT NUMBER := 1;
337 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
338 begin
339 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
340 G_PKG_NAME,
341 p_init_msg_list,
342 l_api_version,
343 p_api_version,
344 '_PUB',
345 x_return_status);
346 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
347 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
348 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
349 RAISE OKC_API.G_EXCEPTION_ERROR;
350 END IF;
351 OKC_PRM_PVT.lock_row(
352 p_init_msg_list,
353 x_return_status,
354 x_msg_count,
355 x_msg_data,
356 p_prmv_rec);
357 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
358 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
359 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
360 RAISE OKC_API.G_EXCEPTION_ERROR;
361 END IF;
362 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
363 EXCEPTION
364 WHEN OKC_API.G_EXCEPTION_ERROR THEN
365 x_return_status := OKC_API.HANDLE_EXCEPTIONS
366 (substr(l_api_name,1,26),
367 G_PKG_NAME,
368 'OKC_API.G_RET_STS_ERROR',
369 x_msg_count,
370 x_msg_data,
371 '_PUB');
372 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
373 x_return_status := OKC_API.HANDLE_EXCEPTIONS
374 (substr(l_api_name,1,26),
375 G_PKG_NAME,
376 'OKC_API.G_RET_STS_UNEXP_ERROR',
377 x_msg_count,
378 x_msg_data,
379 '_PUB');
380 WHEN OTHERS THEN
381 x_return_status := OKC_API.HANDLE_EXCEPTIONS
382 (substr(l_api_name,1,26),
383 G_PKG_NAME,
384 'OTHERS',
385 x_msg_count,
386 x_msg_data,
387 '_PUB');
388 end lock_parameter;
389
390 procedure lock_parameter(p_api_version IN NUMBER,
391 p_init_msg_list IN VARCHAR2 ,
392 x_return_status OUT NOCOPY VARCHAR2,
393 x_msg_count OUT NOCOPY NUMBER,
394 x_msg_data OUT NOCOPY VARCHAR2,
395 p_prmv_tbl IN prmv_tbl_type) is
396 i NUMBER;
397 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
398 begin
399 OKC_API.init_msg_list(p_init_msg_list);
400 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
401 if (p_prmv_tbl.COUNT>0) then
402 i := p_prmv_tbl.FIRST;
403 LOOP
404 lock_parameter(p_api_version=>p_api_version,
405 p_init_msg_list=>OKC_API.G_FALSE,
406 x_return_status=>l_return_status,
407 x_msg_count=>x_msg_count,
408 x_msg_data=>x_msg_data,
409 p_prmv_rec=>p_prmv_tbl(i));
410 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
411 x_return_status := l_return_status;
412 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
413 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
414 x_return_status := l_return_status;
415 end if;
416 EXIT WHEN (i=p_prmv_tbl.LAST);
417 i := p_prmv_tbl.NEXT(i);
418 END LOOP;
419 end if;
420 exception
421 when others then NULL;
422 end lock_parameter;
423
424 -- for lct only
425 procedure set_sql_id (p_sql_id number) is
426 begin
427 g_sql_id := p_sql_id;
428 end;
429
430 function get_sql_id return number is
431 begin
432 return g_sql_id;
433 end;
434
435 -- for process only
436
437 FUNCTION Count_Params RETURN NUMBER IS
438 BEGIN
439 RETURN G_Params_count;
440 END;
441
442 procedure Set_Params(p_array in JTF_VARCHAR2_TABLE_2000) is
443 i number;
444 c number;
445 begin
446 G_Params_tbl.DELETE;
447 G_Params_count := 0;
448 if ((p_array is not null) and (p_array.count > 0)) then
449 c := p_array.count/2;
450 i := p_array.first;
451 while (G_Params_count < c) loop
452 G_Params_tbl(2*G_Params_count+1) := p_array(i);
453 G_Params_tbl(2*G_Params_count+2) := p_array(i+1);
454 G_Params_count := G_Params_count+1;
455 i := i+2;
456 end loop;
457 end if;
458 end;
459
460 procedure Set_Params2(p_array in name_value_tbl_type,
461 p_xid varchar2 ,
462 p_kid varchar2 ) is
463 i number;
464 c number := p_array.COUNT;
465 l_xid varchar2(1) := '?';
466 l_kid varchar2(1) := '?';
467 begin
468 G_Params_tbl.DELETE;
469 G_Params_count := 0;
470 if (c>0) then
471 i := p_array.FIRST;
472 while (G_Params_count < c) loop
473 G_Params_tbl(2*G_Params_count+1) := p_array(i).NAME;
474 G_Params_tbl(2*G_Params_count+2) := p_array(i).VALUE;
475 G_Params_count := G_Params_count+1;
476 if (p_array(i).NAME = 'xid') then
477 l_xid := '!';
478 end if;
479 if (p_array(i).NAME = 'kid') then
480 l_kid := '!';
481 end if;
482 i := i+1;
483 end loop;
484 end if;
485 if ( (l_xid = '?') and (p_xid is not NULL)) then
486 G_Params_tbl(2*G_Params_count+1) := 'xid';
487 G_Params_tbl(2*G_Params_count+2) := p_xid;
488 G_Params_count := G_Params_count+1;
489 end if;
490 if ( (l_kid = '?') and (p_kid is not NULL)) then
491 G_Params_tbl(2*G_Params_count+1) := 'kid';
492 G_Params_tbl(2*G_Params_count+2) := p_kid;
493 G_Params_count := G_Params_count+1;
494 end if;
495
496 end;
497
498 function Get_Name(p_index in number) return varchar2 is
499 begin
500 if (p_index between 1 and G_Params_count) then
501 return G_Params_tbl(2*p_index-1);
502 else
503 return NULL;
504 end if;
505 end;
506
507 function Get_Value(p_index in number) return varchar2 is
508 begin
509 if (p_index between 1 and G_Params_count) then
510 return G_Params_tbl(2*p_index);
511 else
512 return NULL;
513 end if;
514 end;
515
516 function Get(p_name in varchar2) return varchar2 is
517 i number := 0;
518 c2 number := 2*G_Params_count;
519 begin
520 while (i < c2) loop
521 if (G_Params_tbl(i+1) = p_name) then
522 return G_Params_tbl(i+2);
523 else
524 i := i+2;
525 end if;
526 end loop;
527 return NULL;
528 end;
529
530 function Get_Index(p_name in varchar2) return number is
531 i number := 0;
532 c2 number := 2*G_Params_count;
533 begin
534 while (i < c2) loop
535 if (G_Params_tbl(i+1) = p_name) then
536 return (i+2)/2;
537 else
538 i := i+2;
539 end if;
540 end loop;
541 return NULL;
542 end;
543
544 procedure Reset_Param(p_index in number, p_value in varchar2) is
545 begin
546 if (p_index between 1 and G_Params_count) then
547 G_Params_tbl(2*p_index) := p_value;
548 end if;
549 end;
550
551 END OKC_PARAMETERS_PUB;