[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUPDSFPARAMETERS_PVT
Source
1 PACKAGE BODY OKL_SETUPDSFPARAMETERS_PVT AS
2 /* $Header: OKLRSFRB.pls 120.1 2005/06/03 05:31:09 rirawat noship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE get_rec for: OKL_FNCTN_PRMTRS_V
6 ---------------------------------------------------------------------------
7 PROCEDURE get_rec (
8 p_fprv_rec IN fprv_rec_type,
9 x_return_status OUT NOCOPY VARCHAR2,
10 x_no_data_found OUT NOCOPY BOOLEAN,
11 x_fprv_rec OUT NOCOPY fprv_rec_type
12 ) IS
13 CURSOR okl_fprv_pk_csr (p_id IN NUMBER) IS
14 SELECT
15 ID,
16 OBJECT_VERSION_NUMBER,
17 SFWT_FLAG,
18 DSF_ID,
19 PMR_ID,
20 SEQUENCE_NUMBER,
21 NVL(VALUE,OKL_API.G_MISS_CHAR) VALUE,
22 NVL(INSTRUCTIONS,OKL_API.G_MISS_CHAR) INSTRUCTIONS,
23 FPR_TYPE,
24 CREATED_BY,
25 CREATION_DATE,
26 LAST_UPDATED_BY,
27 LAST_UPDATE_DATE,
28 NVL(LAST_UPDATE_LOGIN, OKL_API.G_MISS_NUM) LAST_UPDATE_LOGIN
29 FROM Okl_Fnctn_Prmtrs_V
30 WHERE okl_Fnctn_Prmtrs_V.id = p_id;
31 l_okl_fprv_pk okl_fprv_pk_csr%ROWTYPE;
32 l_fprv_rec fprv_rec_type;
33 BEGIN
34 x_return_status := OKL_API.G_RET_STS_SUCCESS;
35 x_no_data_found := TRUE;
36
37 -- Get current database values
38 OPEN okl_fprv_pk_csr (p_fprv_rec.id);
39 FETCH okl_fprv_pk_csr INTO
40 l_fprv_rec.ID,
41 l_fprv_rec.OBJECT_VERSION_NUMBER,
42 l_fprv_rec.SFWT_FLAG,
43 l_fprv_rec.DSF_ID,
44 l_fprv_rec.PMR_ID,
45 l_fprv_rec.SEQUENCE_NUMBER,
46 l_fprv_rec.VALUE,
47 l_fprv_rec.INSTRUCTIONS,
48 l_fprv_rec.FPR_TYPE,
49 l_fprv_rec.CREATED_BY,
50 l_fprv_rec.CREATION_DATE,
51 l_fprv_rec.LAST_UPDATED_BY,
52 l_fprv_rec.LAST_UPDATE_DATE,
53 l_fprv_rec.LAST_UPDATE_LOGIN;
54 x_no_data_found := okl_fprv_pk_csr%NOTFOUND;
55 CLOSE okl_fprv_pk_csr;
56 x_fprv_rec := l_fprv_rec;
57 EXCEPTION
58 WHEN OTHERS THEN
59 -- store SQL error message on message stack
60 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
61 p_msg_name => G_UNEXPECTED_ERROR,
62 p_token1 => G_SQLCODE_TOKEN,
63 p_token1_value => sqlcode,
64 p_token2 => G_SQLERRM_TOKEN,
65 p_token2_value => sqlerrm);
66 -- notify UNEXPECTED error for calling API.
67 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
68
69 IF (okl_fprv_pk_csr%ISOPEN) THEN
70 CLOSE okl_fprv_pk_csr;
71 END IF;
72
73 END get_rec;
74
75 ---------------------------------------------------------------------------
76 -- PROCEDURE check_constraints for: OKL_FNCTN_PRMTRS_V
77 -- To verify whether the dates are valid for both formula and operands
78 -- attached to it
79 ---------------------------------------------------------------------------
80 PROCEDURE check_constraints (
81 p_fprv_rec IN fprv_rec_type,
82 x_return_status OUT NOCOPY VARCHAR2,
83 x_valid OUT NOCOPY BOOLEAN
84 ) IS
85 CURSOR okl_fprv_pk_csr (p_dsf_id IN Okl_Fnctn_Prmtrs_V.dsf_id%TYPE,
86 p_sequence_number IN Okl_Fnctn_Prmtrs_V.sequence_number%TYPE
87 ) IS
88 SELECT '1'
89 FROM Okl_Fnctn_Prmtrs_V fpr
90 WHERE fpr.DSF_ID = p_dsf_id
91 AND fpr.SEQUENCE_NUMBER = p_sequence_number;
92
93 l_check VARCHAR2(1) := '?';
94 l_row_not_found BOOLEAN := FALSE;
95 BEGIN
96 x_valid := TRUE;
97 x_return_status := OKL_API.G_RET_STS_SUCCESS;
98
99 -- Check for function parameter sequence
100 OPEN okl_fprv_pk_csr (p_fprv_rec.dsf_id,
101 p_fprv_rec.sequence_number);
102 FETCH okl_fprv_pk_csr INTO l_check;
103 l_row_not_found := okl_fprv_pk_csr%NOTFOUND;
104 CLOSE okl_fprv_pk_csr;
105
106 IF l_row_not_found = FALSE then
107 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
108 p_msg_name => G_SEQUENCE_NUMBER,
109 p_token1 => G_COL_NAME_TOKEN,
110 p_token1_value => 'DSF_ID');
111 x_valid := FALSE;
112 x_return_status := OKL_API.G_RET_STS_ERROR;
113 END IF;
114 EXCEPTION
115 WHEN OTHERS THEN
116 -- store SQL error message on message stack
117 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
118 p_msg_name => G_UNEXPECTED_ERROR,
119 p_token1 => G_SQLCODE_TOKEN,
120 p_token1_value => sqlcode,
121 p_token2 => G_SQLERRM_TOKEN,
122 p_token2_value => sqlerrm);
123 x_valid := FALSE;
124 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
125
126 IF (okl_fprv_pk_csr%ISOPEN) THEN
127 CLOSE okl_fprv_pk_csr;
128 END IF;
129
130
131 END check_constraints;
132
133 ---------------------------------------------------------------------------
134 -- PROCEDURE Validate_Pmr_Id
135 ---------------------------------------------------------------------------
136 -- Start of comments
137 --
138 -- Procedure Name : Validate_Pmr_Id
139 -- Description :
140 -- Business Rules :
141 -- Parameters :
142 -- Version : 1.0
143 -- End of comments
144 ---------------------------------------------------------------------------
145 PROCEDURE Validate_Pmr_Id(
146 p_fprv_rec IN fprv_rec_type,
147 x_return_status OUT NOCOPY VARCHAR2
148 ) IS
149
150 l_dummy VARCHAR2(1) := '?';
151 l_row_not_found BOOLEAN := FALSE;
152 l_token_1 VARCHAR2(1999);
153
154 -- Cursor For OKL_FPR_PMR_FK;
155 CURSOR okl_pmrv_pk_csr (p_id IN OKL_FNCTN_PRMTRS_V.pmr_id%TYPE) IS
156 SELECT '1'
157 FROM OKL_PARAMETERS_V
158 WHERE OKL_PARAMETERS_V.id = p_id;
159
160 BEGIN
161
162 -- initialize return status
163 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
164
165 l_token_1 := Okl_Accounting_Util.Get_Message_Token('OKL_LP_FNCT_PARMS_CRUPD','OKL_PARAMETER');
166
167
168 IF p_fprv_rec.pmr_id = Okc_Api.G_MISS_NUM OR
169 p_fprv_rec.pmr_id IS NULL THEN
170 --Okc_Api.set_message(G_APP_NAME,G_INVALID_KEY);
171 Okc_Api.SET_MESSAGE(p_app_name => Okl_fpr_Pvt.g_app_name
172 ,p_msg_name => Okl_fpr_Pvt.g_required_value
173 ,p_token1 => Okl_fpr_Pvt.g_col_name_token
174 ,p_token1_value => l_token_1);
175 x_return_status := Okc_Api.G_RET_STS_ERROR;
176 END IF;
177
178 -- RPOONUGA001: Modified the if condition to check the validility
179 -- in the case of valid pmr_id passed
180 IF p_fprv_rec.pmr_id IS NOT NULL
181 THEN
182 OPEN okl_pmrv_pk_csr(p_fprv_rec.pmr_id);
183 FETCH okl_pmrv_pk_csr INTO l_dummy;
184 l_row_not_found := okl_pmrv_pk_csr%NOTFOUND;
185 CLOSE okl_pmrv_pk_csr;
186
187 IF l_row_not_found THEN
188 --Okc_Api.set_message(G_APP_NAME,G_INVALID_KEY);
189
190 Okc_Api.set_message(Okl_fpr_Pvt.G_APP_NAME, Okl_fpr_Pvt.G_INVALID_VALUE,Okl_fpr_Pvt.G_COL_NAME_TOKEN,l_token_1);
191
192 /*Okc_Api.SET_MESSAGE(p_app_name => Okl_fpr_Pvt.g_app_name
193 ,p_msg_name => Okl_fpr_Pvt.g_required_value
194 ,p_token1 => Okl_fpr_Pvt.g_col_name_token
195 ,p_token1_value => l_token_1);*/
196 x_return_status := Okc_Api.G_RET_STS_ERROR;
197 END IF;
198 END IF;
199
200 EXCEPTION
201 WHEN G_EXCEPTION_HALT_PROCESSING THEN
202 -- no processing necessary; validation can continue
203 -- with the next column
204 NULL;
205
206 WHEN OTHERS THEN
207 -- store SQL error message on message stack for caller
208 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
209 ,p_msg_name => G_UNEXPECTED_ERROR
210 ,p_token1 => G_SQLCODE_TOKEN
211 ,p_token1_value => SQLCODE
212 ,p_token2 => G_SQLERRM_TOKEN
213 ,p_token2_value => SQLERRM);
214
215 -- notify caller of an UNEXPECTED error
216 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
217
218 -- verify that the cursor was closed
219 IF okl_pmrv_pk_csr%ISOPEN THEN
220 CLOSE okl_pmrv_pk_csr;
221 END IF;
222
223 END Validate_Pmr_Id;
224
225 ---------------------------------------------------------------------------
226 -- PROCEDURE Validate_sequence_number
227 ---------------------------------------------------------------------------
228 -- Start of comments
229 --
230 -- Procedure Name : Validate_sequence_number
231 -- Description :
232 -- Business Rules :
233 -- Parameters :
234 -- Version : 1.0
235 -- End of comments
236 ---------------------------------------------------------------------------
237 PROCEDURE Validate_sequence_number(p_fprv_rec IN fprv_rec_type
238 ,x_return_status OUT NOCOPY VARCHAR2 )
239 IS
240
241 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
242 l_token_1 VARCHAR2(1999);
243
244 BEGIN
245 -- initialize return status
246 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
247
248 l_token_1 := Okl_Accounting_Util.Get_Message_Token('OKL_LP_FNCT_PARMS_CRUPD','OKL_SEQUENCE');
249
250 -- check for data before processing
251 IF (p_fprv_rec.sequence_number IS NULL) AND (p_fprv_rec.sequence_number = Okc_Api.G_MISS_NUM) THEN
252 Okc_Api.SET_MESSAGE(p_app_name => Okl_fpr_Pvt.g_app_name
253 ,p_msg_name => Okl_fpr_Pvt.g_required_value
254 ,p_token1 => Okl_fpr_Pvt.g_col_name_token
255 ,p_token1_value => l_token_1);
256
257 x_return_status := Okc_Api.G_RET_STS_ERROR;
258 RAISE G_EXCEPTION_HALT_PROCESSING;
259 END IF;
260
261 EXCEPTION
262 WHEN G_EXCEPTION_HALT_PROCESSING THEN
263 -- no processing necessary; validation can continue
264 -- with the next column
265 NULL;
266
267 WHEN OTHERS THEN
268 -- store SQL error message on message stack for caller
269 Okc_Api.SET_MESSAGE(p_app_name => Okl_Pdt_Pvt.g_app_name,
270 p_msg_name => Okl_Pdt_Pvt.g_unexpected_error,
271 p_token1 => Okl_Pdt_Pvt.g_sqlcode_token,
272 p_token1_value => SQLCODE,
273 p_token2 => Okl_Pdt_Pvt.g_sqlerrm_token,
274 p_token2_value => SQLERRM);
275
276 -- notify caller of an UNEXPECTED error
277 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
278
279 END Validate_sequence_number;
280
281 ---------------------------------------------------------------------------
282 -- FUNCTION Validate_Attributes
283 ---------------------------------------------------------------------------
284 -- Start of comments
285 --
286 -- Procedure Name : Validate_Attributes
287 -- Description :
288 -- Business Rules :
289 -- Parameters :
290 -- Version : 1.0
291 -- End of comments
292 ---------------------------------------------------------------------------
293
294 FUNCTION Validate_Attributes (
295 p_fprv_rec IN fprv_rec_type
296 ) RETURN VARCHAR2 IS
297
298 x_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
299 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
300 BEGIN
301 -- call each column-level validation
302
303 -- Validate_Pmr_Id
304 Validate_Pmr_Id(p_fprv_rec, x_return_status);
305 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
306 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
307 -- need to exit
308 l_return_status := x_return_status;
309 RAISE G_EXCEPTION_HALT_PROCESSING;
310 ELSE
311 -- there was an error
312 l_return_status := x_return_status;
313 END IF;
314 END IF;
315
316 -- Validate_sequence_number
317 Validate_sequence_number(p_fprv_rec, x_return_status);
318 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
319 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
320 -- need to exit
321 l_return_status := x_return_status;
322 RAISE G_EXCEPTION_HALT_PROCESSING;
323 ELSE
324 -- there was an error
325 l_return_status := x_return_status;
326 END IF;
327 END IF;
328
329 RETURN(l_return_status);
330 EXCEPTION
331 WHEN G_EXCEPTION_HALT_PROCESSING THEN
332 -- exit with return status
333 NULL;
334 RETURN (l_return_status);
335
336 WHEN OTHERS THEN
337 -- store SQL error message on message stack for caller
338 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
339 p_msg_name => g_unexpected_error,
340 p_token1 => g_sqlcode_token,
341 p_token1_value => SQLCODE,
342 p_token2 => g_sqlerrm_token,
343 p_token2_value => SQLERRM);
344 -- notify caller of an UNEXPECTED error
345 l_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
346
347 RETURN(l_return_status);
348 END Validate_Attributes;
349
350 ---------------------------------------------------------------------------
351 -- PROCEDURE insert_dsfparameters for: OKL_FNCTN_PRMTRS_V
352 ---------------------------------------------------------------------------
353 PROCEDURE insert_dsfparameters(p_api_version IN NUMBER,
354 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
355 x_return_status OUT NOCOPY VARCHAR2,
356 x_msg_count OUT NOCOPY NUMBER,
357 x_msg_data OUT NOCOPY VARCHAR2,
358 p_dsfv_rec IN dsfv_rec_type,
359 p_fprv_rec IN fprv_rec_type,
360 x_fprv_rec OUT NOCOPY fprv_rec_type
361 ) IS
362 l_api_version CONSTANT NUMBER := 1;
363 l_api_name CONSTANT VARCHAR2(30) := 'insert_dsfparameters';
364 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
365 l_valid BOOLEAN;
366 l_dsfv_rec dsfv_rec_type;
367 l_fprv_rec fprv_rec_type;
368 BEGIN
369 x_return_status := OKC_API.G_RET_STS_SUCCESS;
370 l_return_status := OKC_API.START_ACTIVITY(p_api_name => l_api_name,
371 p_pkg_name => G_PKG_NAME,
372 p_init_msg_list => p_init_msg_list,
373 l_api_version => l_api_version,
374 p_api_version => p_api_version,
375 p_api_type => '_PVT',
376 x_return_status => l_return_status);
377 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
378 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
379 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
380 RAISE OKC_API.G_EXCEPTION_ERROR;
381 END IF;
382
383 l_dsfv_rec := p_dsfv_rec;
384 l_fprv_rec := p_fprv_rec;
385
386 l_return_status := Validate_Attributes(l_fprv_rec);
387 --- If any errors happen abort API
388 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
389 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
390 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
391 RAISE Okc_Api.G_EXCEPTION_ERROR;
392 END IF;
393
394 /* call check_constraints to check the validity of this relationship */
395 check_constraints(p_fprv_rec => l_fprv_rec,
396 x_return_status => l_return_status,
397 x_valid => l_valid);
398 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
399 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
400 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) OR
401 (l_return_status = OKL_API.G_RET_STS_SUCCESS AND
402 l_valid <> TRUE) THEN
403 x_return_status := OKL_API.G_RET_STS_ERROR;
404 RAISE OKC_API.G_EXCEPTION_ERROR;
405 END IF;
406
407 /* public api to insert function parameters */
408 okl_fnctn_prmtrs_pub.insert_fnctn_prmtrs(p_api_version => p_api_version,
409 p_init_msg_list => p_init_msg_list,
410 x_return_status => l_return_status,
411 x_msg_count => x_msg_count,
412 x_msg_data => x_msg_data,
413 p_fprv_rec => l_fprv_rec,
414 x_fprv_rec => x_fprv_rec);
415
416 IF l_return_status = OKC_API.G_RET_STS_ERROR THEN
417 RAISE OKC_API.G_EXCEPTION_ERROR;
418 ELSIF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
419 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
420 END IF;
421
422 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count,
423 x_msg_data => x_msg_data);
424 EXCEPTION
425 WHEN OKC_API.G_EXCEPTION_ERROR THEN
426 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
427 p_pkg_name => G_PKG_NAME,
428 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
429 x_msg_count => x_msg_count,
430 x_msg_data => x_msg_data,
431 p_api_type => '_PVT');
432 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
433 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
434 p_pkg_name => G_PKG_NAME,
435 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
436 x_msg_count => x_msg_count,
437 x_msg_data => x_msg_data,
438 p_api_type => '_PVT');
439 WHEN OTHERS THEN
440 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
441 p_pkg_name => G_PKG_NAME,
442 p_exc_name => 'OTHERS',
443 x_msg_count => x_msg_count,
444 x_msg_data => x_msg_data,
445 p_api_type => '_PVT');
446
447 END insert_dsfparameters;
448
449 ---------------------------------------------------------------------------
450 -- PROCEDURE update_dsfparameters for: OKL_FNCTN_PRMTRS_V
451 ---------------------------------------------------------------------------
452 PROCEDURE update_dsfparameters(p_api_version IN NUMBER,
453 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
454 x_return_status OUT NOCOPY VARCHAR2,
455 x_msg_count OUT NOCOPY NUMBER,
456 x_msg_data OUT NOCOPY VARCHAR2,
457 p_dsfv_rec IN dsfv_rec_type,
458 p_fprv_rec IN fprv_rec_type,
459 x_fprv_rec OUT NOCOPY fprv_rec_type
460 ) IS
461 l_api_version CONSTANT NUMBER := 1;
462 l_api_name CONSTANT VARCHAR2(30) := 'update_dsfparameters';
463 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
464 l_valid BOOLEAN;
465 l_dsfv_rec dsfv_rec_type;
466 l_fprv_rec fprv_rec_type;
467 BEGIN
468 x_return_status := OKC_API.G_RET_STS_SUCCESS;
469 l_return_status := OKC_API.START_ACTIVITY(p_api_name => l_api_name,
470 p_pkg_name => G_PKG_NAME,
471 p_init_msg_list => p_init_msg_list,
472 l_api_version => l_api_version,
473 p_api_version => p_api_version,
474 p_api_type => '_PVT',
475 x_return_status => l_return_status);
476 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
477 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
478 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
479 RAISE OKC_API.G_EXCEPTION_ERROR;
480 END IF;
481
482 l_dsfv_rec := p_dsfv_rec;
483 l_fprv_rec := p_fprv_rec;
484
485 /* call check_constraints to check the validity of this relationship */
486 check_constraints(p_fprv_rec => l_fprv_rec,
487 x_return_status => l_return_status,
488 x_valid => l_valid);
489 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
490 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
491 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) OR
492 (l_return_status = OKL_API.G_RET_STS_SUCCESS AND
493 l_valid <> TRUE) THEN
494 x_return_status := OKL_API.G_RET_STS_ERROR;
495 RAISE OKC_API.G_EXCEPTION_ERROR;
496 END IF;
497
498 /* public api to insert function parameters */
499 okl_fnctn_prmtrs_pub.update_fnctn_prmtrs(p_api_version => p_api_version,
500 p_init_msg_list => p_init_msg_list,
501 x_return_status => l_return_status,
502 x_msg_count => x_msg_count,
503 x_msg_data => x_msg_data,
504 p_fprv_rec => l_fprv_rec,
505 x_fprv_rec => x_fprv_rec);
506
507 IF l_return_status = OKC_API.G_RET_STS_ERROR THEN
508 RAISE OKC_API.G_EXCEPTION_ERROR;
509 ELSIF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
510 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
511 END IF;
512
513 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count,
514 x_msg_data => x_msg_data);
515 EXCEPTION
516 WHEN OKC_API.G_EXCEPTION_ERROR THEN
517 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
518 p_pkg_name => G_PKG_NAME,
519 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
520 x_msg_count => x_msg_count,
521 x_msg_data => x_msg_data,
522 p_api_type => '_PVT');
523 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
524 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
525 p_pkg_name => G_PKG_NAME,
526 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
527 x_msg_count => x_msg_count,
528 x_msg_data => x_msg_data,
529 p_api_type => '_PVT');
530 WHEN OTHERS THEN
531 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
532 p_pkg_name => G_PKG_NAME,
533 p_exc_name => 'OTHERS',
534 x_msg_count => x_msg_count,
535 x_msg_data => x_msg_data,
536 p_api_type => '_PVT');
537
538 END update_dsfparameters;
539
540
541 ---------------------------------------------------------------------------
542 -- PROCEDURE delete_dsfparameters for: OKL_FNCTN_PRMTRS_V
543 -- This allows the user to delete table of records
544 ---------------------------------------------------------------------------
545 PROCEDURE delete_dsfparameters(p_api_version IN NUMBER,
546 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
547 x_return_status OUT NOCOPY VARCHAR2,
548 x_msg_count OUT NOCOPY NUMBER,
549 x_msg_data OUT NOCOPY VARCHAR2,
550 p_fprv_tbl IN fprv_tbl_type
551 ) IS
552 l_api_version CONSTANT NUMBER := 1;
553 l_fprv_tbl fprv_tbl_type;
554 l_api_name CONSTANT VARCHAR2(30) := 'delete_dsfparameters';
555 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
556
557 BEGIN
558 x_return_status := OKC_API.G_RET_STS_SUCCESS;
559 l_return_status := OKC_API.START_ACTIVITY(p_api_name => l_api_name,
560 p_pkg_name => G_PKG_NAME,
561 p_init_msg_list => p_init_msg_list,
562 l_api_version => l_api_version,
563 p_api_version => p_api_version,
564 p_api_type => '_PVT',
565 x_return_status => l_return_status);
566 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
567 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
568 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
569 RAISE OKC_API.G_EXCEPTION_ERROR;
570 END IF;
571
572 l_fprv_tbl := p_fprv_tbl;
573
574 /* delete formulae constraints */
575 okl_fnctn_prmtrs_pub.delete_fnctn_prmtrs(p_api_version => p_api_version,
576 p_init_msg_list => p_init_msg_list,
577 x_return_status => l_return_status,
578 x_msg_count => x_msg_count,
579 x_msg_data => x_msg_data,
580 p_fprv_tbl => l_fprv_tbl);
581
582 IF l_return_status = OKC_API.G_RET_STS_ERROR THEN
583 RAISE OKC_API.G_EXCEPTION_ERROR;
584 ELSIF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
585 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
586 END IF;
587
588 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count,
589 x_msg_data => x_msg_data);
590 EXCEPTION
591 WHEN OKC_API.G_EXCEPTION_ERROR THEN
592 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
593 p_pkg_name => G_PKG_NAME,
594 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
595 x_msg_count => x_msg_count,
596 x_msg_data => x_msg_data,
597 p_api_type => '_PVT');
598 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
599 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
600 p_pkg_name => G_PKG_NAME,
601 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
602 x_msg_count => x_msg_count,
603 x_msg_data => x_msg_data,
604 p_api_type => '_PVT');
605 WHEN OTHERS THEN
606 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
607 p_pkg_name => G_PKG_NAME,
608 p_exc_name => 'OTHERS',
609 x_msg_count => x_msg_count,
610 x_msg_data => x_msg_data,
611 p_api_type => '_PVT');
612
613 END delete_dsfparameters;
614
615 -- rirawat 03-Feb-05 4149748: Added the following procedure to insert into OKL_FNCTN_PRMTRS_V - TBL : begin
616 ---------------------------------------------------------------------------
617 -- PROCEDURE insert_dsfparameters for: OKL_FNCTN_PRMTRS_V - TBL
618 ---------------------------------------------------------------------------
619 PROCEDURE insert_dsfparameters(
620 p_api_version IN NUMBER,
621 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
622 x_return_status OUT NOCOPY VARCHAR2,
623 x_msg_count OUT NOCOPY NUMBER,
624 x_msg_data OUT NOCOPY VARCHAR2,
625 p_dsfv_rec IN dsfv_rec_type,
626 p_fprv_tbl IN fprv_tbl_type,
627 x_fprv_tbl OUT NOCOPY fprv_tbl_type) IS
628
629 l_api_version CONSTANT NUMBER := 1;
630 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_dsfparameters';
631 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
632 i NUMBER := 0;
633 -- overall error status
634 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
635
636 BEGIN
637 Okl_Api.init_msg_list(p_init_msg_list);
638 -- Make sure PL/SQL table has records in it before passing
639 IF (p_fprv_tbl.COUNT > 0) THEN
640 i := p_fprv_tbl.FIRST;
641 LOOP
642 insert_dsfparameters (
643 p_api_version => p_api_version,
644 p_init_msg_list => Okl_Api.G_FALSE,
645 x_return_status => x_return_status,
646 x_msg_count => x_msg_count,
647 p_dsfv_rec => p_dsfv_rec,
648 x_msg_data => x_msg_data,
649 p_fprv_rec => p_fprv_tbl(i),
650 x_fprv_rec => x_fprv_tbl(i));
651
652 -- store the highest degree of error
653 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
654 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
655 l_overall_status := x_return_status;
656 END IF;
657 END IF;
658
659
660 EXIT WHEN (i = p_fprv_tbl.LAST);
661 i := p_fprv_tbl.NEXT(i);
662 END LOOP;
663
664 -- return overall status
665 x_return_status := l_overall_status;
666
667
668 END IF;
669 EXCEPTION
670 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
671 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
672 (
673 l_api_name,
674 G_PKG_NAME,
675 'Okl_Api.G_RET_STS_ERROR',
676 x_msg_count,
677 x_msg_data,
678 '_PVT'
679 );
680 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
681 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
682 (
683 l_api_name,
684 G_PKG_NAME,
685 'Okl_Api.G_RET_STS_UNEXP_ERROR',
686 x_msg_count,
687 x_msg_data,
688 '_PVT'
689 );
690 WHEN OTHERS THEN
691 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
692 (
693 l_api_name,
694 G_PKG_NAME,
695 'OTHERS',
696 x_msg_count,
697 x_msg_data,
698 '_PVT'
699 );
700 END insert_dsfparameters;
701
702 -- rirawat 03-Feb-05 4149748: Added the following procedure to insert into OKL_FNCTN_PRMTRS_V - TBL : end
703
704 -- rirawat 03-Feb-05 4149748: Added the following procedure to update into OKL_FNCTN_PRMTRS_V - TBL : begin
705 ---------------------------------------------------------------------------
706 -- PROCEDURE update_dsfparameters for: OKL_FNCTN_PRMTRS_V -TBL
707 ---------------------------------------------------------------------------
708 PROCEDURE update_dsfparameters(
709 p_api_version IN NUMBER,
710 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
711 x_return_status OUT NOCOPY VARCHAR2,
712 x_msg_count OUT NOCOPY NUMBER,
713 x_msg_data OUT NOCOPY VARCHAR2,
714 p_dsfv_rec IN dsfv_rec_type,
715 p_fprv_tbl IN fprv_tbl_type,
716 x_fprv_tbl OUT NOCOPY fprv_tbl_type) IS
717
718 l_api_version CONSTANT NUMBER := 1;
719 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_dsfparameters';
720 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
721 i NUMBER := 0;
722 -- overall error status
723 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
724
725 BEGIN
726
727 -- Make sure PL/SQL table has records in it before passing
728 IF (p_fprv_tbl.COUNT > 0) THEN
729 i := p_fprv_tbl.FIRST;
730 LOOP
731
732 update_dsfparameters (
733 p_api_version => p_api_version,
734 p_init_msg_list => Okl_Api.G_FALSE,
735 x_return_status => x_return_status,
736 x_msg_count => x_msg_count,
737 p_dsfv_rec => p_dsfv_rec,
738 x_msg_data => x_msg_data,
739 p_fprv_rec => p_fprv_tbl(i),
740 x_fprv_rec => x_fprv_tbl(i));
741
742
743 -- store the highest degree of error
744 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
745 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
746 l_overall_status := x_return_status;
747 END IF;
748 END IF;
749
750
751 EXIT WHEN (i = p_fprv_tbl.LAST);
752 i := p_fprv_tbl.NEXT(i);
753 END LOOP;
754
755 -- return overall status
756 x_return_status := l_overall_status;
757
758
759 END IF;
760 EXCEPTION
761 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
762 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
763 (
764 l_api_name,
765 G_PKG_NAME,
766 'Okl_Api.G_RET_STS_ERROR',
767 x_msg_count,
768 x_msg_data,
769 '_PVT'
770 );
771 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
772 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
773 (
774 l_api_name,
775 G_PKG_NAME,
776 'Okl_Api.G_RET_STS_UNEXP_ERROR',
777 x_msg_count,
778 x_msg_data,
779 '_PVT'
780 );
781 WHEN OTHERS THEN
782 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
783 (
784 l_api_name,
785 G_PKG_NAME,
786 'OTHERS',
787 x_msg_count,
788 x_msg_data,
789 '_PVT'
790 );
791 END update_dsfparameters;
792
793 -- rirawat 03-Feb-05 4149748: Added the following procedure to update into OKL_FNCTN_PRMTRS_V - TBL : end
794
795
796 END OKL_SETUPDSFPARAMETERS_PVT;