[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUPCGRPARAMETERS_PVT
Source
1 PACKAGE BODY OKL_SETUPCGRPARAMETERS_PVT AS
2 /* $Header: OKLRSCMB.pls 120.1 2005/06/03 05:30:33 rirawat noship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE get_rec for: OKL_CNTX_GRP_PRMTRS_V
6 ---------------------------------------------------------------------------
7 PROCEDURE get_rec (
8 p_cgmv_rec IN cgmv_rec_type,
9 x_return_status OUT NOCOPY VARCHAR2,
10 x_no_data_found OUT NOCOPY BOOLEAN,
11 x_cgmv_rec OUT NOCOPY cgmv_rec_type
12 ) IS
13 CURSOR okl_cgmv_pk_csr (p_id IN NUMBER) IS
14 SELECT
15 ID,
16 OBJECT_VERSION_NUMBER,
17 CGR_ID,
18 PMR_ID,
19 CREATED_BY,
20 CREATION_DATE,
21 LAST_UPDATED_BY,
22 LAST_UPDATE_DATE,
23 NVL(LAST_UPDATE_LOGIN, OKL_API.G_MISS_NUM) LAST_UPDATE_LOGIN
24 FROM Okl_Cntx_Grp_Prmtrs_V
25 WHERE okl_Cntx_Grp_Prmtrs_V.id = p_id;
26 l_okl_cgmv_pk okl_cgmv_pk_csr%ROWTYPE;
27 l_cgmv_rec cgmv_rec_type;
28 BEGIN
29 x_return_status := OKL_API.G_RET_STS_SUCCESS;
30 x_no_data_found := TRUE;
31
32 -- Get current database values
33 OPEN okl_cgmv_pk_csr (p_cgmv_rec.id);
34 FETCH okl_cgmv_pk_csr INTO
35 l_cgmv_rec.ID,
36 l_cgmv_rec.OBJECT_VERSION_NUMBER,
37 l_cgmv_rec.CGR_ID,
38 l_cgmv_rec.PMR_ID,
39 l_cgmv_rec.CREATED_BY,
40 l_cgmv_rec.CREATION_DATE,
41 l_cgmv_rec.LAST_UPDATED_BY,
42 l_cgmv_rec.LAST_UPDATE_DATE,
43 l_cgmv_rec.LAST_UPDATE_LOGIN;
44 x_no_data_found := okl_cgmv_pk_csr%NOTFOUND;
45 CLOSE okl_cgmv_pk_csr;
46 x_cgmv_rec := l_cgmv_rec;
47 EXCEPTION
48 WHEN OTHERS THEN
49 -- store SQL error message on message stack
50 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
51 p_msg_name => G_UNEXPECTED_ERROR,
52 p_token1 => G_SQLCODE_TOKEN,
53 p_token1_value => sqlcode,
54 p_token2 => G_SQLERRM_TOKEN,
55 p_token2_value => sqlerrm);
56 -- notify UNEXPECTED error for calling API.
57 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
58
59 IF (okl_cgmv_pk_csr%ISOPEN) THEN
60 CLOSE okl_cgmv_pk_csr;
61 END IF;
62
63 END get_rec;
64
65 ---------------------------------------------------------------------------
66 -- PROCEDURE check_context_groups for: OKL_CNTX_GRP_PRMTRS_V
67 -- To verify whether context group under consideration is being attached to
68 -- any formula
69 ---------------------------------------------------------------------------
70 PROCEDURE check_context_groups (
71 p_cgmv_rec IN cgmv_rec_type,
72 x_return_status OUT NOCOPY VARCHAR2,
73 x_valid OUT NOCOPY BOOLEAN
74 ) IS
75 CURSOR okl_fmav_fk_csr (p_cgr_id IN Okl_Formulae_V.cgr_id%TYPE
76 ) IS
77 SELECT '1'
78 FROM Okl_Formulae_V fma
79 WHERE fma.CGR_ID = p_cgr_id;
80
81 l_check VARCHAR2(1) := '?';
82 l_row_not_found BOOLEAN := FALSE;
83 BEGIN
84 x_valid := TRUE;
85 x_return_status := OKL_API.G_RET_STS_SUCCESS;
86
87 -- Check for related formulae
88 OPEN okl_fmav_fk_csr (p_cgmv_rec.cgr_id);
89 FETCH okl_fmav_fk_csr INTO l_check;
90 l_row_not_found := okl_fmav_fk_csr%NOTFOUND;
91 CLOSE okl_fmav_fk_csr;
92
93 IF l_row_not_found = FALSE then
94 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
95 p_msg_name => G_IN_USE,
96 p_token1 => G_PARENT_TABLE_TOKEN,
97 p_token1_value => 'Okl_Formulae_V',
98 p_token2 => G_CHILD_TABLE_TOKEN,
99 p_token2_value => 'Okl_Context_Groups_V');
100 x_valid := FALSE;
101 x_return_status := OKL_API.G_RET_STS_ERROR;
102 END IF;
103
104 EXCEPTION
105 WHEN OTHERS THEN
106 -- store SQL error message on message stack
107 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
108 p_msg_name => G_UNEXPECTED_ERROR,
109 p_token1 => G_SQLCODE_TOKEN,
110 p_token1_value => sqlcode,
111 p_token2 => G_SQLERRM_TOKEN,
112 p_token2_value => sqlerrm);
113 x_valid := FALSE;
114 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
115
116 IF (okl_fmav_fk_csr%ISOPEN) THEN
117 CLOSE okl_fmav_fk_csr;
118 END IF;
119
120
121 END check_context_groups;
122
123 ---------------------------------------------------------------------------
124 -- PROCEDURE insert_cgrparameters for: OKL_CNTX_GRP_PRMTRS_V
125 ---------------------------------------------------------------------------
126 PROCEDURE insert_cgrparameters(p_api_version IN NUMBER,
127 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
128 x_return_status OUT NOCOPY VARCHAR2,
129 x_msg_count OUT NOCOPY NUMBER,
130 x_msg_data OUT NOCOPY VARCHAR2,
131 p_cgmv_rec IN cgmv_rec_type,
132 x_cgmv_rec OUT NOCOPY cgmv_rec_type
133 ) IS
134 l_api_version CONSTANT NUMBER := 1;
135 l_api_name CONSTANT VARCHAR2(30) := 'insert_cgrparameters';
136 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
137 l_cgmv_rec cgmv_rec_type;
138 BEGIN
139 x_return_status := OKC_API.G_RET_STS_SUCCESS;
140 l_return_status := OKC_API.START_ACTIVITY(p_api_name => l_api_name,
141 p_pkg_name => G_PKG_NAME,
142 p_init_msg_list => p_init_msg_list,
143 l_api_version => l_api_version,
144 p_api_version => p_api_version,
145 p_api_type => '_PVT',
146 x_return_status => l_return_status);
147 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
148 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
149 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
150 RAISE OKC_API.G_EXCEPTION_ERROR;
151 END IF;
152
153 l_cgmv_rec := p_cgmv_rec;
154
155 /* public api to insert context group parameters */
156 okl_cntx_grp_prmtrs_pub.insert_cntx_grp_prmtrs(p_api_version => p_api_version,
157 p_init_msg_list => p_init_msg_list,
158 x_return_status => l_return_status,
159 x_msg_count => x_msg_count,
160 x_msg_data => x_msg_data,
161 p_cgmv_rec => l_cgmv_rec,
162 x_cgmv_rec => x_cgmv_rec);
163
164 IF l_return_status = OKC_API.G_RET_STS_ERROR THEN
165 RAISE OKC_API.G_EXCEPTION_ERROR;
166 ELSIF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
167 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
168 END IF;
169
170 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count,
171 x_msg_data => x_msg_data);
172 EXCEPTION
173 WHEN OKC_API.G_EXCEPTION_ERROR THEN
174 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
175 p_pkg_name => G_PKG_NAME,
176 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
177 x_msg_count => x_msg_count,
178 x_msg_data => x_msg_data,
179 p_api_type => '_PVT');
180 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
181 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
182 p_pkg_name => G_PKG_NAME,
183 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
184 x_msg_count => x_msg_count,
185 x_msg_data => x_msg_data,
186 p_api_type => '_PVT');
187 WHEN OTHERS THEN
188 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
189 p_pkg_name => G_PKG_NAME,
190 p_exc_name => 'OTHERS',
191 x_msg_count => x_msg_count,
192 x_msg_data => x_msg_data,
193 p_api_type => '_PVT');
194
195 END insert_cgrparameters;
196
197 ---------------------------------------------------------------------------
198 -- PROCEDURE update_cgrparameters for: OKL_CNTX_GRP_PRMTRS_V
199 ---------------------------------------------------------------------------
200 PROCEDURE update_cgrparameters(p_api_version IN NUMBER,
201 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
202 x_return_status OUT NOCOPY VARCHAR2,
203 x_msg_count OUT NOCOPY NUMBER,
204 x_msg_data OUT NOCOPY VARCHAR2,
205 p_cgmv_rec IN cgmv_rec_type,
206 x_cgmv_rec OUT NOCOPY cgmv_rec_type
207 ) IS
208 l_api_version CONSTANT NUMBER := 1;
209 l_api_name CONSTANT VARCHAR2(30) := 'update_cgrparameters';
210 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
211 l_valid BOOLEAN;
212 l_cgmv_rec cgmv_rec_type;
213 BEGIN
214 x_return_status := OKC_API.G_RET_STS_SUCCESS;
215 l_return_status := OKC_API.START_ACTIVITY(p_api_name => l_api_name,
216 p_pkg_name => G_PKG_NAME,
217 p_init_msg_list => p_init_msg_list,
218 l_api_version => l_api_version,
219 p_api_version => p_api_version,
220 p_api_type => '_PVT',
221 x_return_status => l_return_status);
222 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
223 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
224 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
225 RAISE OKC_API.G_EXCEPTION_ERROR;
226 END IF;
227
228 l_cgmv_rec := p_cgmv_rec;
229
230 /* call check_context_groups to check whether it is affecting existing formulae */
231 check_context_groups(p_cgmv_rec => l_cgmv_rec,
232 x_return_status => l_return_status,
233 x_valid => l_valid);
234 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
235 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
236 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) OR
237 (l_return_status = OKL_API.G_RET_STS_SUCCESS AND
238 l_valid <> TRUE) THEN
239 x_return_status := OKL_API.G_RET_STS_ERROR;
240 RAISE OKC_API.G_EXCEPTION_ERROR;
241 END IF;
242
243 /* public api to update context groups */
244 okl_cntx_grp_prmtrs_pub.update_cntx_grp_prmtrs(p_api_version => p_api_version,
245 p_init_msg_list => p_init_msg_list,
246 x_return_status => l_return_status,
247 x_msg_count => x_msg_count,
248 x_msg_data => x_msg_data,
249 p_cgmv_rec => l_cgmv_rec,
250 x_cgmv_rec => x_cgmv_rec);
251
252 IF l_return_status = OKC_API.G_RET_STS_ERROR THEN
253 RAISE OKC_API.G_EXCEPTION_ERROR;
254 ELSIF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
255 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
256 END IF;
257
258 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count,
259 x_msg_data => x_msg_data);
260 EXCEPTION
261 WHEN OKC_API.G_EXCEPTION_ERROR THEN
262 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
263 p_pkg_name => G_PKG_NAME,
264 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
265 x_msg_count => x_msg_count,
266 x_msg_data => x_msg_data,
267 p_api_type => '_PVT');
268 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
269 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
270 p_pkg_name => G_PKG_NAME,
271 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
272 x_msg_count => x_msg_count,
273 x_msg_data => x_msg_data,
274 p_api_type => '_PVT');
275 WHEN OTHERS THEN
276 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
277 p_pkg_name => G_PKG_NAME,
278 p_exc_name => 'OTHERS',
279 x_msg_count => x_msg_count,
280 x_msg_data => x_msg_data,
281 p_api_type => '_PVT');
282
283 END update_cgrparameters;
284
285
286 ---------------------------------------------------------------------------
287 -- PROCEDURE delete_cgrparameters for: OKL_CNTX_GRP_PRMTRS_V
288 -- This allows the user to delete table of records
289 ---------------------------------------------------------------------------
290 PROCEDURE delete_cgrparameters(p_api_version IN NUMBER,
291 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
292 x_return_status OUT NOCOPY VARCHAR2,
293 x_msg_count OUT NOCOPY NUMBER,
294 x_msg_data OUT NOCOPY VARCHAR2,
295 p_cgmv_tbl IN cgmv_tbl_type
296 ) IS
297 l_api_version CONSTANT NUMBER := 1;
298 l_cgmv_tbl cgmv_tbl_type;
299 l_api_name CONSTANT VARCHAR2(30) := 'delete_cgrparameters';
300 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
301 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
302 l_valid BOOLEAN;
303 i NUMBER;
304
305 BEGIN
306 x_return_status := OKC_API.G_RET_STS_SUCCESS;
307 l_return_status := OKC_API.START_ACTIVITY(p_api_name => l_api_name,
308 p_pkg_name => G_PKG_NAME,
309 p_init_msg_list => p_init_msg_list,
310 l_api_version => l_api_version,
311 p_api_version => p_api_version,
312 p_api_type => '_PVT',
313 x_return_status => l_return_status);
314 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
315 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
316 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
317 RAISE OKC_API.G_EXCEPTION_ERROR;
318 END IF;
319
320 l_cgmv_tbl := p_cgmv_tbl;
321 IF (l_cgmv_tbl.COUNT > 0) THEN
322 i := l_cgmv_tbl.FIRST;
323
324 LOOP
325 check_context_groups(p_cgmv_rec => l_cgmv_tbl(i),
326 x_return_status => l_return_status,
327 x_valid => l_valid);
328 -- store the highest degree of error
329 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
330 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
331 l_overall_status := l_return_status;
332 END IF;
333 END IF;
334 EXIT WHEN (i = l_cgmv_tbl.LAST);
335
336 i := l_cgmv_tbl.NEXT(i);
337
338 END LOOP;
339
340 -- return overall status
341 l_return_status := l_overall_status;
342 END IF;
343 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
344 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
345 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) OR
346 (l_return_status = OKL_API.G_RET_STS_SUCCESS AND
347 l_valid <> TRUE) THEN
348 x_return_status := OKL_API.G_RET_STS_ERROR;
349 RAISE OKC_API.G_EXCEPTION_ERROR;
350 END IF;
351
352 /* delete context group parameters */
353 okl_cntx_grp_prmtrs_pub.delete_cntx_grp_prmtrs(p_api_version => p_api_version,
354 p_init_msg_list => p_init_msg_list,
355 x_return_status => l_return_status,
356 x_msg_count => x_msg_count,
357 x_msg_data => x_msg_data,
358 p_cgmv_tbl => l_cgmv_tbl);
359
360 IF l_return_status = OKC_API.G_RET_STS_ERROR THEN
361 RAISE OKC_API.G_EXCEPTION_ERROR;
362 ELSIF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
363 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
364 END IF;
365
366 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count,
367 x_msg_data => x_msg_data);
368 EXCEPTION
369 WHEN OKC_API.G_EXCEPTION_ERROR THEN
370 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
371 p_pkg_name => G_PKG_NAME,
372 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
373 x_msg_count => x_msg_count,
374 x_msg_data => x_msg_data,
375 p_api_type => '_PVT');
376 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
377 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
378 p_pkg_name => G_PKG_NAME,
379 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
380 x_msg_count => x_msg_count,
381 x_msg_data => x_msg_data,
382 p_api_type => '_PVT');
383 WHEN OTHERS THEN
384 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
385 p_pkg_name => G_PKG_NAME,
386 p_exc_name => 'OTHERS',
387 x_msg_count => x_msg_count,
388 x_msg_data => x_msg_data,
389 p_api_type => '_PVT');
390
391 END delete_cgrparameters;
392
393 -- rirawat 03-Feb-05 4149748: Added the following procedure to insert into OKL_CNTX_GRP_PRMTRS_V - TBL : begin
394 ---------------------------------------------------------------------------
395 -- PROCEDURE insert_cgrparameters for: OKL_CNTX_GRP_PRMTRS_V
396 ---------------------------------------------------------------------------
397 PROCEDURE insert_cgrparameters(p_api_version IN NUMBER,
398 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
399 x_return_status OUT NOCOPY VARCHAR2,
400 x_msg_count OUT NOCOPY NUMBER,
401 x_msg_data OUT NOCOPY VARCHAR2,
402 p_cgmv_tbl IN cgmv_tbl_type,
403 x_cgmv_tbl OUT NOCOPY cgmv_tbl_type
404 ) IS
405 l_api_version CONSTANT NUMBER := 1;
406 l_api_name CONSTANT VARCHAR2(30) := 'insert_cgrparameters';
407 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
408 l_cgmv_rec cgmv_rec_type;
409 i NUMBER := 0;
410 BEGIN
411 x_return_status := OKC_API.G_RET_STS_SUCCESS;
412 l_return_status := OKC_API.START_ACTIVITY(p_api_name => l_api_name,
413 p_pkg_name => G_PKG_NAME,
414 p_init_msg_list => p_init_msg_list,
415 l_api_version => l_api_version,
416 p_api_version => p_api_version,
417 p_api_type => '_PVT',
418 x_return_status => l_return_status);
419 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
420 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
421 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
422 RAISE OKC_API.G_EXCEPTION_ERROR;
423 END IF;
424
425 IF (p_cgmv_tbl.COUNT > 0) THEN
426 i := p_cgmv_tbl.FIRST;
427 LOOP
428 l_cgmv_rec := p_cgmv_tbl(i);
429
430 /* public api to insert context group parameters */
431 okl_cntx_grp_prmtrs_pub.insert_cntx_grp_prmtrs(p_api_version => p_api_version,
432 p_init_msg_list => p_init_msg_list,
433 x_return_status => l_return_status,
434 x_msg_count => x_msg_count,
435 x_msg_data => x_msg_data,
436 p_cgmv_rec => l_cgmv_rec,
437 x_cgmv_rec => x_cgmv_tbl(i));
438
439 IF l_return_status = OKC_API.G_RET_STS_ERROR THEN
440 RAISE OKC_API.G_EXCEPTION_ERROR;
441 ELSIF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
442 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
443 END IF;
444
445 EXIT WHEN (i = p_cgmv_tbl.LAST);
446 i := p_cgmv_tbl.NEXT(i);
447 END LOOP;
448
449 END IF;
450
451 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count,
452 x_msg_data => x_msg_data);
453 EXCEPTION
454 WHEN OKC_API.G_EXCEPTION_ERROR THEN
455 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
456 p_pkg_name => G_PKG_NAME,
457 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
458 x_msg_count => x_msg_count,
459 x_msg_data => x_msg_data,
460 p_api_type => '_PVT');
461 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
462 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
463 p_pkg_name => G_PKG_NAME,
464 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
465 x_msg_count => x_msg_count,
466 x_msg_data => x_msg_data,
467 p_api_type => '_PVT');
468 WHEN OTHERS THEN
469 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
470 p_pkg_name => G_PKG_NAME,
471 p_exc_name => 'OTHERS',
472 x_msg_count => x_msg_count,
473 x_msg_data => x_msg_data,
474 p_api_type => '_PVT');
475
476 END insert_cgrparameters;
477 -- rirawat 03-Feb-05 4149748: Added the following procedure to insert into OKL_CNTX_GRP_PRMTRS_V - TBL : end
478
479 -- rirawat 03-Feb-05 4149748: Added the following procedure to update into OKL_CNTX_GRP_PRMTRS_V - TBL : begin
480 ---------------------------------------------------------------------------
481 -- PROCEDURE update_cgrparameters for: OKL_CNTX_GRP_PRMTRS_V
482 ---------------------------------------------------------------------------
483 PROCEDURE update_cgrparameters(p_api_version IN NUMBER,
484 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
485 x_return_status OUT NOCOPY VARCHAR2,
486 x_msg_count OUT NOCOPY NUMBER,
487 x_msg_data OUT NOCOPY VARCHAR2,
488 p_cgmv_tbl IN cgmv_tbl_type,
489 x_cgmv_tbl OUT NOCOPY cgmv_tbl_type
490 ) IS
491 l_api_version CONSTANT NUMBER := 1;
492 l_api_name CONSTANT VARCHAR2(30) := 'update_cgrparameters';
493 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
494 l_valid BOOLEAN;
495 l_cgmv_rec cgmv_rec_type;
496 i NUMBER := 0;
497 BEGIN
498 x_return_status := OKC_API.G_RET_STS_SUCCESS;
499 l_return_status := OKC_API.START_ACTIVITY(p_api_name => l_api_name,
500 p_pkg_name => G_PKG_NAME,
501 p_init_msg_list => p_init_msg_list,
502 l_api_version => l_api_version,
503 p_api_version => p_api_version,
504 p_api_type => '_PVT',
505 x_return_status => l_return_status);
506 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
507 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
508 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
509 RAISE OKC_API.G_EXCEPTION_ERROR;
510 END IF;
511
512
513 IF (p_cgmv_tbl.COUNT > 0) THEN
514 i := p_cgmv_tbl.FIRST;
515 LOOP
516
517 l_cgmv_rec := p_cgmv_tbl(i);
518
519 /* call check_context_groups to check whether it is affecting existing formulae */
520 check_context_groups(p_cgmv_rec => l_cgmv_rec,
521 x_return_status => l_return_status,
522 x_valid => l_valid);
523 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
524 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
525 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) OR
526 (l_return_status = OKL_API.G_RET_STS_SUCCESS AND
527 l_valid <> TRUE) THEN
528 x_return_status := OKL_API.G_RET_STS_ERROR;
529 RAISE OKC_API.G_EXCEPTION_ERROR;
530 END IF;
531
532 /* public api to update context groups */
533 okl_cntx_grp_prmtrs_pub.update_cntx_grp_prmtrs(p_api_version => p_api_version,
534 p_init_msg_list => p_init_msg_list,
535 x_return_status => l_return_status,
536 x_msg_count => x_msg_count,
537 x_msg_data => x_msg_data,
538 p_cgmv_rec => l_cgmv_rec,
539 x_cgmv_rec => x_cgmv_tbl(i));
540
541 IF l_return_status = OKC_API.G_RET_STS_ERROR THEN
542 RAISE OKC_API.G_EXCEPTION_ERROR;
543 ELSIF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
544 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
545 END IF;
546
547 EXIT WHEN (i = p_cgmv_tbl.LAST);
548 i := p_cgmv_tbl.NEXT(i);
549 END LOOP;
550
551 END IF;
552
553 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count,
554 x_msg_data => x_msg_data);
555 EXCEPTION
556 WHEN OKC_API.G_EXCEPTION_ERROR THEN
557 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
558 p_pkg_name => G_PKG_NAME,
559 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
560 x_msg_count => x_msg_count,
561 x_msg_data => x_msg_data,
562 p_api_type => '_PVT');
563 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
564 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
565 p_pkg_name => G_PKG_NAME,
566 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
567 x_msg_count => x_msg_count,
568 x_msg_data => x_msg_data,
569 p_api_type => '_PVT');
570 WHEN OTHERS THEN
571 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
572 p_pkg_name => G_PKG_NAME,
573 p_exc_name => 'OTHERS',
574 x_msg_count => x_msg_count,
575 x_msg_data => x_msg_data,
576 p_api_type => '_PVT');
577
578 END update_cgrparameters;
579
580 -- rirawat 03-Feb-05 4149748: Added the following procedure to update into OKL_CNTX_GRP_PRMTRS_V - TBL : end
581
582
583 END OKL_SETUPCGRPARAMETERS_PVT;