[Home] [Help]
PACKAGE BODY: APPS.OKL_RPP_PVT
Source
1 PACKAGE BODY OKL_RPP_PVT AS
2 /* $Header: OKLSRPPB.pls 120.4 2007/12/31 11:05:40 dcshanmu noship $ */
3 ---------------------------------------------------------------------------
4 -- PROCEDURE load_error_tbl
5 ---------------------------------------------------------------------------
6 PROCEDURE load_error_tbl (
7 px_error_rec IN OUT NOCOPY OKL_API.ERROR_REC_TYPE,
8 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
9
10 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
11 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
12 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
13 BEGIN
14 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
15 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
16 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
17 -- automatically increments the index by 1, (making it 2), however, when the GET function
18 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn't any
19 -- message 2. To circumvent this problem, check the amount of messages and compensate.
20 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
21 -- will only update the index variable when 1 and only 1 message is on the stack.
22 IF (last_msg_idx = 1) THEN
23 l_msg_idx := FND_MSG_PUB.G_FIRST;
24 END IF;
25 LOOP
26 fnd_msg_pub.get(
27 p_msg_index => l_msg_idx,
28 p_encoded => fnd_api.g_false,
29 p_data => px_error_rec.msg_data,
30 p_msg_index_out => px_error_rec.msg_count);
31 px_error_tbl(j) := px_error_rec;
32 j := j + 1;
33 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
34 END LOOP;
35 END load_error_tbl;
36 ---------------------------------------------------------------------------
37 -- FUNCTION find_highest_exception
38 ---------------------------------------------------------------------------
39 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
40 -- in a OKL_API.ERROR_TBL_TYPE, and returns it.
41 FUNCTION find_highest_exception(
42 p_error_tbl IN OKL_API.ERROR_TBL_TYPE
43 ) RETURN VARCHAR2 IS
44 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
45 i INTEGER := 1;
46 BEGIN
47 IF (p_error_tbl.COUNT > 0) THEN
48 i := p_error_tbl.FIRST;
49 LOOP
50 IF (p_error_tbl(i).error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
51 IF (l_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
52 l_return_status := p_error_tbl(i).error_type;
53 END IF;
54 END IF;
55 EXIT WHEN (i = p_error_tbl.LAST);
56 i := p_error_tbl.NEXT(i);
57 END LOOP;
58 END IF;
59 RETURN(l_return_status);
60 END find_highest_exception;
61 ---------------------------------------------------------------------------
62 -- FUNCTION get_seq_id
63 ---------------------------------------------------------------------------
64 FUNCTION get_seq_id RETURN NUMBER IS
65 l_pk_value NUMBER;
66 CURSOR c_pk_csr IS SELECT okl_report_parameters_s.NEXTVAL FROM DUAL;
67 BEGIN
68 /* Fetch the pk value from the sequence */
69 OPEN c_pk_csr;
70 FETCH c_pk_csr INTO l_pk_value;
71 CLOSE c_pk_csr;
72 RETURN l_pk_value;
73 END get_seq_id;
74
75 ---------------------------------------------------------------------------
76 -- PROCEDURE qc
77 ---------------------------------------------------------------------------
78 PROCEDURE qc IS
79 BEGIN
80 null;
81 END qc;
82
83 ---------------------------------------------------------------------------
84 -- PROCEDURE change_version
85 ---------------------------------------------------------------------------
86 PROCEDURE change_version IS
87 BEGIN
88 null;
89 END change_version;
90
91 ---------------------------------------------------------------------------
92 -- PROCEDURE api_copy
93 ---------------------------------------------------------------------------
94 PROCEDURE api_copy IS
95 BEGIN
96 null;
97 END api_copy;
98
99 ---------------------------------------------------------------------------
100 -- FUNCTION get_rec for: OKL_REPORT_PARAMETERS
101 ---------------------------------------------------------------------------
102 FUNCTION get_rec (
103 p_rpp_rec IN rpp_rec_type,
104 x_no_data_found OUT NOCOPY BOOLEAN
105 ) RETURN rpp_rec_type IS
106 CURSOR okl_report_parameters_pk_csr (p_parameter_id IN NUMBER) IS
107 SELECT
108 PARAMETER_ID,
109 REPORT_ID,
110 PARAMETER_TYPE_CODE,
111 PARAM_NUM_VALUE1,
112 PARAM_CHAR_VALUE1,
113 PARAM_DATE_VALUE1,
114 CREATED_BY,
115 SOURCE_TABLE,
116 CREATION_DATE,
117 LAST_UPDATED_BY,
118 LAST_UPDATE_DATE,
119 LAST_UPDATE_LOGIN
120 FROM Okl_Report_Parameters
121 WHERE okl_report_parameters.parameter_id = p_parameter_id;
122 l_okl_report_parameters_pk okl_report_parameters_pk_csr%ROWTYPE;
123 l_rpp_rec rpp_rec_type;
124 BEGIN
125 x_no_data_found := TRUE;
126 -- Get current database values
127 OPEN okl_report_parameters_pk_csr (p_rpp_rec.parameter_id);
128 FETCH okl_report_parameters_pk_csr INTO
129 l_rpp_rec.parameter_id,
130 l_rpp_rec.report_id,
131 l_rpp_rec.parameter_type,
132 l_rpp_rec.param_num_value1,
133 l_rpp_rec.param_char_value1,
134 l_rpp_rec.param_date_value1,
135 l_rpp_rec.created_by,
136 l_rpp_rec.source_table,
137 l_rpp_rec.creation_date,
138 l_rpp_rec.last_updated_by,
139 l_rpp_rec.last_update_date,
140 l_rpp_rec.last_update_login;
141 x_no_data_found := okl_report_parameters_pk_csr%NOTFOUND;
142 CLOSE okl_report_parameters_pk_csr;
143 RETURN(l_rpp_rec);
144 END get_rec;
145
146 ------------------------------------------------------------------
147 -- This version of get_rec sets error messages if no data found --
148 ------------------------------------------------------------------
149 FUNCTION get_rec (
150 p_rpp_rec IN rpp_rec_type,
151 x_return_status OUT NOCOPY VARCHAR2
152 ) RETURN rpp_rec_type IS
153 l_rpp_rec rpp_rec_type;
154 l_row_notfound BOOLEAN := TRUE;
155 BEGIN
156 x_return_status := OKL_API.G_RET_STS_SUCCESS;
157 l_rpp_rec := get_rec(p_rpp_rec, l_row_notfound);
158 IF (l_row_notfound) THEN
159 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'PARAMETER_ID');
160 x_return_status := OKL_API.G_RET_STS_ERROR;
161 END IF;
162 RETURN(l_rpp_rec);
163 END get_rec;
164 -----------------------------------------------------------
165 -- So we don't have to pass an "l_row_notfound" variable --
166 -----------------------------------------------------------
167 FUNCTION get_rec (
168 p_rpp_rec IN rpp_rec_type
169 ) RETURN rpp_rec_type IS
170 l_row_not_found BOOLEAN := TRUE;
171 BEGIN
172 RETURN(get_rec(p_rpp_rec, l_row_not_found));
173 END get_rec;
174 ---------------------------------------------------------------------------
175 -- FUNCTION null_out_defaults for: OKL_REPORT_PARAMETERS
176 ---------------------------------------------------------------------------
177 FUNCTION null_out_defaults (
178 p_rpp_rec IN rpp_rec_type
179 ) RETURN rpp_rec_type IS
180 l_rpp_rec rpp_rec_type := p_rpp_rec;
181 BEGIN
182 IF (l_rpp_rec.parameter_id = OKL_API.G_MISS_NUM ) THEN
183 l_rpp_rec.parameter_id := NULL;
184 END IF;
185 IF (l_rpp_rec.report_id = OKL_API.G_MISS_NUM ) THEN
186 l_rpp_rec.report_id := NULL;
187 END IF;
188 IF (l_rpp_rec.parameter_type = OKL_API.G_MISS_CHAR ) THEN
189 l_rpp_rec.parameter_type := NULL;
190 END IF;
191 IF (l_rpp_rec.param_num_value1 = OKL_API.G_MISS_NUM ) THEN
192 l_rpp_rec.param_num_value1 := NULL;
193 END IF;
194 IF (l_rpp_rec.param_char_value1 = OKL_API.G_MISS_CHAR ) THEN
195 l_rpp_rec.param_char_value1 := NULL;
196 END IF;
197 IF (l_rpp_rec.param_date_value1 = OKL_API.G_MISS_DATE ) THEN
198 l_rpp_rec.param_date_value1 := NULL;
199 END IF;
200 IF (l_rpp_rec.created_by = OKL_API.G_MISS_NUM ) THEN
201 l_rpp_rec.created_by := NULL;
202 END IF;
203 IF (l_rpp_rec.source_table = OKL_API.G_MISS_CHAR ) THEN
204 l_rpp_rec.source_table := NULL;
205 END IF;
206 IF (l_rpp_rec.creation_date = OKL_API.G_MISS_DATE ) THEN
207 l_rpp_rec.creation_date := NULL;
208 END IF;
209 IF (l_rpp_rec.last_updated_by = OKL_API.G_MISS_NUM ) THEN
210 l_rpp_rec.last_updated_by := NULL;
211 END IF;
212 IF (l_rpp_rec.last_update_date = OKL_API.G_MISS_DATE ) THEN
213 l_rpp_rec.last_update_date := NULL;
214 END IF;
215 IF (l_rpp_rec.last_update_login = OKL_API.G_MISS_NUM ) THEN
216 l_rpp_rec.last_update_login := NULL;
217 END IF;
218 RETURN(l_rpp_rec);
219 END null_out_defaults;
220 -------------------------------------------
221 -- Validate_Attributes for: PARAMETER_ID --
222 -------------------------------------------
223 PROCEDURE validate_parameter_id(
224 x_return_status OUT NOCOPY VARCHAR2,
225 p_parameter_id IN NUMBER) IS
226 BEGIN
227 x_return_status := OKL_API.G_RET_STS_SUCCESS;
228 IF (p_parameter_id IS NULL) THEN
229 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'parameter_id');
230 x_return_status := OKL_API.G_RET_STS_ERROR;
231 RAISE G_EXCEPTION_HALT_VALIDATION;
232 END IF;
233 EXCEPTION
234 WHEN G_EXCEPTION_HALT_VALIDATION THEN
235 null;
236 WHEN OTHERS THEN
237 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
238 ,p_msg_name => G_UNEXPECTED_ERROR
239 ,p_token1 => G_SQLCODE_TOKEN
240 ,p_token1_value => SQLCODE
241 ,p_token2 => G_SQLERRM_TOKEN
242 ,p_token2_value => SQLERRM);
243 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
244 END validate_parameter_id;
245 ----------------------------------------
246 -- Validate_Attributes for: REPORT_ID --
247 ----------------------------------------
248 PROCEDURE validate_report_id(
249 x_return_status OUT NOCOPY VARCHAR2,
250 p_report_id IN NUMBER) IS
251 BEGIN
252 x_return_status := OKL_API.G_RET_STS_SUCCESS;
253 IF (p_report_id IS NULL) THEN
254 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'report_id');
255 x_return_status := OKL_API.G_RET_STS_ERROR;
256 RAISE G_EXCEPTION_HALT_VALIDATION;
257 END IF;
258 EXCEPTION
259 WHEN G_EXCEPTION_HALT_VALIDATION THEN
260 null;
261 WHEN OTHERS THEN
262 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
263 ,p_msg_name => G_UNEXPECTED_ERROR
264 ,p_token1 => G_SQLCODE_TOKEN
265 ,p_token1_value => SQLCODE
266 ,p_token2 => G_SQLERRM_TOKEN
267 ,p_token2_value => SQLERRM);
268 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
269 END validate_report_id;
270 ---------------------------------------------
271 -- Validate_Attributes for: PARAMETER_TYPE --
272 ---------------------------------------------
273 PROCEDURE validate_parameter_type(
274 x_return_status OUT NOCOPY VARCHAR2,
275 p_parameter_type IN VARCHAR2) IS
276 BEGIN
277 x_return_status := OKL_API.G_RET_STS_SUCCESS;
278 IF (p_parameter_type IS NULL) THEN
279 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'parameter_type');
280 x_return_status := OKL_API.G_RET_STS_ERROR;
281 RAISE G_EXCEPTION_HALT_VALIDATION;
282 END IF;
283 EXCEPTION
284 WHEN G_EXCEPTION_HALT_VALIDATION THEN
285 null;
286 WHEN OTHERS THEN
287 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
288 ,p_msg_name => G_UNEXPECTED_ERROR
289 ,p_token1 => G_SQLCODE_TOKEN
290 ,p_token1_value => SQLCODE
291 ,p_token2 => G_SQLERRM_TOKEN
292 ,p_token2_value => SQLERRM);
293 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
294 END validate_parameter_type;
295 ---------------------------------------------------------------------------
296 -- FUNCTION Validate_Attributes
297 ---------------------------------------------------------------------------
298 ---------------------------------------------------
299 -- Validate_Attributes for:OKL_REPORT_PARAMETERS --
300 ---------------------------------------------------
301 FUNCTION Validate_Attributes (
302 p_rpp_rec IN rpp_rec_type
303 ) RETURN VARCHAR2 IS
304
305 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
306 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
307 l_test_value NUMBER := 0;
308
309 -- cursor to validate report id and parameter type code combination
310 CURSOR c_is_valid_report_param_comb(
311 p_report_id OKL_REPORT_PARAMETERS.REPORT_ID%TYPE
312 ,p_param_type_code OKL_REPORT_PARAMETERS.PARAMETER_TYPE_CODE%TYPE) IS
313 select 1
314 from okl_report_parameters a,
315 fnd_lookups b
316 where b.lookup_type='OKL_PARAMETER_TYPES'
317 and b.lookup_code = a.parameter_type_code
318 and a.parameter_type_code = p_param_type_code
319 and a.report_id = p_report_id;
320
321 -- cursor to validate book classification code
322 CURSOR c_is_valid_bcc(p_bcc_value OKL_REPORTS_B.BOOK_CLASSIFICATION_CODE%TYPE) IS
323 SELECT 1
324 FROM FND_LOOKUPS
325 WHERE LOOKUP_TYPE = 'OKL_BOOK_CLASS'
326 AND LOOKUP_CODE = p_bcc_value;
327
328 BEGIN
329 -----------------------------
330 -- Column Level Validation --
331 -----------------------------
332 -- ***
333 -- parameter_id
334 -- ***
335 validate_parameter_id(x_return_status, p_rpp_rec.parameter_id);
336 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
337 l_return_status := x_return_status;
338 RAISE G_EXCEPTION_HALT_VALIDATION;
339 END IF;
340
341 -- ***
342 -- report_id
343 -- ***
344 validate_report_id(x_return_status, p_rpp_rec.report_id);
345 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
346 l_return_status := x_return_status;
347 RAISE G_EXCEPTION_HALT_VALIDATION;
348 END IF;
349
350 -- ***
351 -- parameter_type
352 -- ***
353 validate_parameter_type(x_return_status, p_rpp_rec.parameter_type);
354 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
355 l_return_status := x_return_status;
356 RAISE G_EXCEPTION_HALT_VALIDATION;
357 END IF;
358
359 -- validate book classification code
360 IF (p_rpp_rec.param_char_value1 IS NOT NULL OR p_rpp_rec.param_char_value1 <> OKL_API.G_MISS_CHAR) THEN
361 OPEN c_is_valid_bcc(p_rpp_rec.param_char_value1);
362
363 FETCH c_is_valid_bcc INTO l_test_value;
364
365 IF (c_is_valid_bcc%NOTFOUND) THEN
366 OKL_API.set_message(p_app_name => G_APP_NAME,
367 p_msg_name => G_COL_ERROR,
368 p_token1 => G_COL_NAME_TOKEN,
369 p_token1_value => 'book_classification_code',
370 p_token2 => G_PKG_NAME_TOKEN,
371 p_token2_value => G_PKG_NAME);
372 CLOSE c_is_valid_bcc;
373 RAISE OKL_API.G_EXCEPTION_ERROR;
374
375 END IF;
376
377 CLOSE c_is_valid_bcc;
378 END IF;
379
380 -- validate report id and parameter type code combination
381 OPEN c_is_valid_report_param_comb(p_rpp_rec.report_id, p_rpp_rec.parameter_type);
382 FETCH c_is_valid_report_param_comb INTO l_test_value;
383
384 IF (c_is_valid_report_param_comb%FOUND) THEN
385 OKL_API.set_message(p_app_name => G_APP_NAME,
386 p_msg_name => 'OKL_RPP_COMB_EXISTS_MSG');
387 CLOSE c_is_valid_report_param_comb;
388 RAISE OKL_API.G_EXCEPTION_ERROR;
389 END IF;
390 CLOSE c_is_valid_report_param_comb;
391
392 RETURN(l_return_status);
393 EXCEPTION
394 WHEN G_EXCEPTION_HALT_VALIDATION THEN
395 RETURN(l_return_status);
396 WHEN OTHERS THEN
397 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
398 ,p_msg_name => G_UNEXPECTED_ERROR
399 ,p_token1 => G_SQLCODE_TOKEN
400 ,p_token1_value => SQLCODE
401 ,p_token2 => G_SQLERRM_TOKEN
402 ,p_token2_value => SQLERRM);
403 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
404 RETURN(l_return_status);
405 END Validate_Attributes;
406 ---------------------------------------------------------------------------
407 -- PROCEDURE Validate_Record
408 ---------------------------------------------------------------------------
409 -----------------------------------------------
410 -- Validate Record for:OKL_REPORT_PARAMETERS --
411 -----------------------------------------------
412 FUNCTION Validate_Record (
413 p_rpp_rec IN rpp_rec_type,
414 p_db_rpp_rec IN rpp_rec_type
415 ) RETURN VARCHAR2 IS
416 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
417 BEGIN
418 RETURN (l_return_status);
419 END Validate_Record;
420 FUNCTION Validate_Record (
421 p_rpp_rec IN rpp_rec_type
422 ) RETURN VARCHAR2 IS
423 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
424 l_db_rpp_rec rpp_rec_type := get_rec(p_rpp_rec);
425 BEGIN
426 l_return_status := Validate_Record(p_rpp_rec => p_rpp_rec,
427 p_db_rpp_rec => l_db_rpp_rec);
428 RETURN (l_return_status);
429 END Validate_Record;
430
431 ---------------------------------------------------------------------------
432 -- PROCEDURE Migrate
433 ---------------------------------------------------------------------------
434 ---------------------------------------------------------------------------
435 -- PROCEDURE validate_row
436 ---------------------------------------------------------------------------
437 --------------------------------------------
438 -- validate_row for:OKL_REPORT_PARAMETERS --
439 --------------------------------------------
440 PROCEDURE validate_row(
441 p_api_version IN NUMBER,
442 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
443 x_return_status OUT NOCOPY VARCHAR2,
444 x_msg_count OUT NOCOPY NUMBER,
445 x_msg_data OUT NOCOPY VARCHAR2,
446 p_rpp_rec IN rpp_rec_type) IS
447
448 l_api_version CONSTANT NUMBER := 1;
449 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
450 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
451 l_rpp_rec rpp_rec_type := p_rpp_rec;
452 BEGIN
453 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
454 G_PKG_NAME,
455 p_init_msg_list,
456 l_api_version,
457 p_api_version,
458 '_PVT',
459 x_return_status);
460 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
461 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
462 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
463 RAISE OKL_API.G_EXCEPTION_ERROR;
464 END IF;
465 --- Validate all non-missing attributes (Item Level Validation)
466 l_return_status := Validate_Attributes(l_rpp_rec);
467 --- If any errors happen abort API
468 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
469 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
470 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
471 RAISE OKL_API.G_EXCEPTION_ERROR;
472 END IF;
473 l_return_status := Validate_Record(l_rpp_rec);
474 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
475 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
476 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
477 RAISE OKL_API.G_EXCEPTION_ERROR;
478 END IF;
479 x_return_status := l_return_status;
480 EXCEPTION
481 WHEN OKL_API.G_EXCEPTION_ERROR THEN
482 x_return_status := OKL_API.HANDLE_EXCEPTIONS
483 (
484 l_api_name,
485 G_PKG_NAME,
486 'OKL_API.G_RET_STS_ERROR',
487 x_msg_count,
488 x_msg_data,
489 '_PVT'
490 );
491 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
492 x_return_status := OKL_API.HANDLE_EXCEPTIONS
493 (
494 l_api_name,
495 G_PKG_NAME,
496 'OKL_API.G_RET_STS_UNEXP_ERROR',
497 x_msg_count,
498 x_msg_data,
499 '_PVT'
500 );
501 WHEN OTHERS THEN
502 x_return_status := OKL_API.HANDLE_EXCEPTIONS
503 (
504 l_api_name,
505 G_PKG_NAME,
506 'OTHERS',
507 x_msg_count,
508 x_msg_data,
509 '_PVT'
510 );
511 END validate_row;
512 -------------------------------------------------------
513 -- PL/SQL TBL validate_row for:OKL_REPORT_PARAMETERS --
514 -------------------------------------------------------
515 PROCEDURE validate_row(
516 p_api_version IN NUMBER,
517 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
518 x_return_status OUT NOCOPY VARCHAR2,
519 x_msg_count OUT NOCOPY NUMBER,
520 x_msg_data OUT NOCOPY VARCHAR2,
521 p_rpp_tbl IN rpp_tbl_type,
522 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
523
524 l_api_version CONSTANT NUMBER := 1;
525 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
526 i NUMBER := 0;
527 BEGIN
528 OKL_API.init_msg_list(p_init_msg_list);
529 -- Make sure PL/SQL table has records in it before passing
530 IF (p_rpp_tbl.COUNT > 0) THEN
531 i := p_rpp_tbl.FIRST;
532 LOOP
533 DECLARE
534 l_error_rec OKL_API.ERROR_REC_TYPE;
535 BEGIN
536 l_error_rec.api_name := l_api_name;
537 l_error_rec.api_package := G_PKG_NAME;
538 l_error_rec.idx := i;
539 validate_row (
540 p_api_version => p_api_version,
541 p_init_msg_list => OKL_API.G_FALSE,
542 x_return_status => l_error_rec.error_type,
543 x_msg_count => l_error_rec.msg_count,
544 x_msg_data => l_error_rec.msg_data,
545 p_rpp_rec => p_rpp_tbl(i));
546 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
547 l_error_rec.sqlcode := SQLCODE;
548 load_error_tbl(l_error_rec, px_error_tbl);
549 ELSE
550 x_msg_count := l_error_rec.msg_count;
551 x_msg_data := l_error_rec.msg_data;
552 END IF;
553 EXCEPTION
554 WHEN OKL_API.G_EXCEPTION_ERROR THEN
555 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
556 l_error_rec.sqlcode := SQLCODE;
557 load_error_tbl(l_error_rec, px_error_tbl);
558 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
559 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
560 l_error_rec.sqlcode := SQLCODE;
561 load_error_tbl(l_error_rec, px_error_tbl);
562 WHEN OTHERS THEN
563 l_error_rec.error_type := 'OTHERS';
564 l_error_rec.sqlcode := SQLCODE;
565 load_error_tbl(l_error_rec, px_error_tbl);
566 END;
567 EXIT WHEN (i = p_rpp_tbl.LAST);
568 i := p_rpp_tbl.NEXT(i);
569 END LOOP;
570 END IF;
571 -- Loop through the error_tbl to find the error with the highest severity
572 -- and return it.
573 x_return_status := find_highest_exception(px_error_tbl);
574 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
575 EXCEPTION
576 WHEN OKL_API.G_EXCEPTION_ERROR THEN
577 x_return_status := OKL_API.HANDLE_EXCEPTIONS
578 (
579 l_api_name,
580 G_PKG_NAME,
581 'OKL_API.G_RET_STS_ERROR',
582 x_msg_count,
583 x_msg_data,
584 '_PVT'
585 );
586 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
587 x_return_status := OKL_API.HANDLE_EXCEPTIONS
588 (
589 l_api_name,
590 G_PKG_NAME,
591 'OKL_API.G_RET_STS_UNEXP_ERROR',
592 x_msg_count,
593 x_msg_data,
594 '_PVT'
595 );
596 WHEN OTHERS THEN
597 x_return_status := OKL_API.HANDLE_EXCEPTIONS
598 (
599 l_api_name,
600 G_PKG_NAME,
601 'OTHERS',
602 x_msg_count,
603 x_msg_data,
604 '_PVT'
605 );
606 END validate_row;
607
608 -------------------------------------------------------
609 -- PL/SQL TBL validate_row for:OKL_REPORT_PARAMETERS --
610 -------------------------------------------------------
611 PROCEDURE validate_row(
612 p_api_version IN NUMBER,
613 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
614 x_return_status OUT NOCOPY VARCHAR2,
615 x_msg_count OUT NOCOPY NUMBER,
616 x_msg_data OUT NOCOPY VARCHAR2,
617 p_rpp_tbl IN rpp_tbl_type) IS
618
619 l_api_version CONSTANT NUMBER := 1;
620 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
621 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
622 l_error_tbl OKL_API.ERROR_TBL_TYPE;
623 BEGIN
624 OKL_API.init_msg_list(p_init_msg_list);
625 -- Make sure PL/SQL table has records in it before passing
626 IF (p_rpp_tbl.COUNT > 0) THEN
627 validate_row (
628 p_api_version => p_api_version,
629 p_init_msg_list => OKL_API.G_FALSE,
630 x_return_status => x_return_status,
631 x_msg_count => x_msg_count,
632 x_msg_data => x_msg_data,
633 p_rpp_tbl => p_rpp_tbl,
634 px_error_tbl => l_error_tbl);
635 END IF;
636 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
637 EXCEPTION
638 WHEN OKL_API.G_EXCEPTION_ERROR THEN
639 x_return_status := OKL_API.HANDLE_EXCEPTIONS
640 (
641 l_api_name,
642 G_PKG_NAME,
643 'OKL_API.G_RET_STS_ERROR',
644 x_msg_count,
645 x_msg_data,
646 '_PVT'
647 );
648 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
649 x_return_status := OKL_API.HANDLE_EXCEPTIONS
650 (
651 l_api_name,
652 G_PKG_NAME,
653 'OKL_API.G_RET_STS_UNEXP_ERROR',
654 x_msg_count,
655 x_msg_data,
656 '_PVT'
657 );
658 WHEN OTHERS THEN
659 x_return_status := OKL_API.HANDLE_EXCEPTIONS
660 (
661 l_api_name,
662 G_PKG_NAME,
663 'OTHERS',
664 x_msg_count,
665 x_msg_data,
666 '_PVT'
667 );
668 END validate_row;
669
670 ---------------------------------------------------------------------------
671 -- PROCEDURE insert_row
672 ---------------------------------------------------------------------------
673 ------------------------------------------
674 -- insert_row for:OKL_REPORT_PARAMETERS --
675 ------------------------------------------
676 PROCEDURE insert_row(
677 p_api_version IN NUMBER,
678 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
679 x_return_status OUT NOCOPY VARCHAR2,
680 x_msg_count OUT NOCOPY NUMBER,
681 x_msg_data OUT NOCOPY VARCHAR2,
682 p_rpp_rec IN rpp_rec_type,
683 x_rpp_rec OUT NOCOPY rpp_rec_type) IS
684
685 l_api_version CONSTANT NUMBER := 1;
686 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
687 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
688 l_rpp_rec rpp_rec_type := p_rpp_rec;
689 l_def_rpp_rec rpp_rec_type;
690 -------------------------------
691 -- FUNCTION fill_who_columns --
692 -------------------------------
693 FUNCTION fill_who_columns (
694 p_rpp_rec IN rpp_rec_type
695 ) RETURN rpp_rec_type IS
696 l_rpp_rec rpp_rec_type := p_rpp_rec;
697 BEGIN
698 l_rpp_rec.CREATION_DATE := SYSDATE;
699 l_rpp_rec.CREATED_BY := FND_GLOBAL.USER_ID;
700 l_rpp_rec.LAST_UPDATE_DATE := l_rpp_rec.CREATION_DATE;
701 l_rpp_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
702 l_rpp_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
703 RETURN(l_rpp_rec);
704 END fill_who_columns;
705 ----------------------------------------------
706 -- Set_Attributes for:OKL_REPORT_PARAMETERS --
707 ----------------------------------------------
708 FUNCTION Set_Attributes (
709 p_rpp_rec IN rpp_rec_type,
710 x_rpp_rec OUT NOCOPY rpp_rec_type
711 ) RETURN VARCHAR2 IS
712 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
713 BEGIN
714 x_rpp_rec := p_rpp_rec;
715 RETURN(l_return_status);
716 END Set_Attributes;
717 BEGIN
718 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
719 G_PKG_NAME,
720 p_init_msg_list,
721 l_api_version,
722 p_api_version,
723 '_PVT',
724 x_return_status);
725 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
726 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
727 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
728 RAISE OKL_API.G_EXCEPTION_ERROR;
729 END IF;
730 l_rpp_rec := null_out_defaults(p_rpp_rec);
731 -- Set primary key value
732 l_rpp_rec.PARAMETER_ID := get_seq_id;
733 -- Setting item attributes
734 l_return_Status := Set_Attributes(
735 l_rpp_rec, -- IN
736 l_def_rpp_rec); -- OUT
737 --- If any errors happen abort API
738 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
739 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
740 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
741 RAISE OKL_API.G_EXCEPTION_ERROR;
742 END IF;
743 l_def_rpp_rec := fill_who_columns(l_def_rpp_rec);
744 --- Validate all non-missing attributes (Item Level Validation)
745 l_return_status := Validate_Attributes(l_def_rpp_rec);
746 --- If any errors happen abort API
747 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
748 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
749 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
750 RAISE OKL_API.G_EXCEPTION_ERROR;
751 END IF;
752 l_return_status := Validate_Record(l_def_rpp_rec);
753 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
754 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
755 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
756 RAISE OKL_API.G_EXCEPTION_ERROR;
757 END IF;
758 INSERT INTO OKL_REPORT_PARAMETERS(
759 parameter_id,
760 report_id,
761 parameter_type_code,
762 param_num_value1,
763 param_char_value1,
764 param_date_value1,
765 created_by,
766 source_table,
767 creation_date,
768 last_updated_by,
769 last_update_date,
770 last_update_login)
771 VALUES (
772 l_def_rpp_rec.parameter_id,
773 l_def_rpp_rec.report_id,
774 l_def_rpp_rec.parameter_type,
775 l_def_rpp_rec.param_num_value1,
776 l_def_rpp_rec.param_char_value1,
777 l_def_rpp_rec.param_date_value1,
778 l_def_rpp_rec.created_by,
779 l_def_rpp_rec.source_table,
780 l_def_rpp_rec.creation_date,
781 l_def_rpp_rec.last_updated_by,
782 l_def_rpp_rec.last_update_date,
783 l_def_rpp_rec.last_update_login);
784 -- Set OUT values
785 x_rpp_rec := l_def_rpp_rec;
786 x_return_status := l_return_status;
787 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
788 EXCEPTION
789 WHEN OKL_API.G_EXCEPTION_ERROR THEN
790 x_return_status := OKL_API.HANDLE_EXCEPTIONS
791 (
792 l_api_name,
793 G_PKG_NAME,
794 'OKL_API.G_RET_STS_ERROR',
795 x_msg_count,
796 x_msg_data,
797 '_PVT'
798 );
799 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
800 x_return_status := OKL_API.HANDLE_EXCEPTIONS
801 (
802 l_api_name,
803 G_PKG_NAME,
804 'OKL_API.G_RET_STS_UNEXP_ERROR',
805 x_msg_count,
806 x_msg_data,
807 '_PVT'
808 );
809 WHEN OTHERS THEN
810 x_return_status := OKL_API.HANDLE_EXCEPTIONS
811 (
812 l_api_name,
813 G_PKG_NAME,
814 'OTHERS',
815 x_msg_count,
816 x_msg_data,
817 '_PVT'
818 );
819 END insert_row;
820 ---------------------------------------
821 -- PL/SQL TBL insert_row for:RPP_TBL --
822 ---------------------------------------
823 PROCEDURE insert_row(
824 p_api_version IN NUMBER,
825 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
826 x_return_status OUT NOCOPY VARCHAR2,
827 x_msg_count OUT NOCOPY NUMBER,
828 x_msg_data OUT NOCOPY VARCHAR2,
829 p_rpp_tbl IN rpp_tbl_type,
830 x_rpp_tbl OUT NOCOPY rpp_tbl_type,
831 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
832
833 l_api_version CONSTANT NUMBER := 1;
834 l_api_name CONSTANT VARCHAR2(30) := 'error_tbl_insert_row';
835 i NUMBER := 0;
836 BEGIN
837 OKL_API.init_msg_list(p_init_msg_list);
838 -- Make sure PL/SQL table has records in it before passing
839 IF (p_rpp_tbl.COUNT > 0) THEN
840 i := p_rpp_tbl.FIRST;
841 LOOP
842 DECLARE
843 l_error_rec OKL_API.ERROR_REC_TYPE;
844 BEGIN
845 l_error_rec.api_name := l_api_name;
846 l_error_rec.api_package := G_PKG_NAME;
847 l_error_rec.idx := i;
848 insert_row (
849 p_api_version => p_api_version,
850 p_init_msg_list => OKL_API.G_FALSE,
851 x_return_status => l_error_rec.error_type,
852 x_msg_count => l_error_rec.msg_count,
853 x_msg_data => l_error_rec.msg_data,
854 p_rpp_rec => p_rpp_tbl(i),
855 x_rpp_rec => x_rpp_tbl(i));
856 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
857 l_error_rec.sqlcode := SQLCODE;
858 load_error_tbl(l_error_rec, px_error_tbl);
859 ELSE
860 x_msg_count := l_error_rec.msg_count;
861 x_msg_data := l_error_rec.msg_data;
862 END IF;
863 EXCEPTION
864 WHEN OKL_API.G_EXCEPTION_ERROR THEN
865 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
866 l_error_rec.sqlcode := SQLCODE;
867 load_error_tbl(l_error_rec, px_error_tbl);
868 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
869 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
870 l_error_rec.sqlcode := SQLCODE;
871 load_error_tbl(l_error_rec, px_error_tbl);
872 WHEN OTHERS THEN
873 l_error_rec.error_type := 'OTHERS';
874 l_error_rec.sqlcode := SQLCODE;
875 load_error_tbl(l_error_rec, px_error_tbl);
876 END;
877 EXIT WHEN (i = p_rpp_tbl.LAST);
878 i := p_rpp_tbl.NEXT(i);
879 END LOOP;
880 END IF;
881 -- Loop through the error_tbl to find the error with the highest severity
882 -- and return it.
883 x_return_status := find_highest_exception(px_error_tbl);
884 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
885 EXCEPTION
886 WHEN OKL_API.G_EXCEPTION_ERROR THEN
887 x_return_status := OKL_API.HANDLE_EXCEPTIONS
888 (
889 l_api_name,
890 G_PKG_NAME,
891 'OKL_API.G_RET_STS_ERROR',
892 x_msg_count,
893 x_msg_data,
894 '_PVT'
895 );
896 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
897 x_return_status := OKL_API.HANDLE_EXCEPTIONS
898 (
899 l_api_name,
900 G_PKG_NAME,
901 'OKL_API.G_RET_STS_UNEXP_ERROR',
902 x_msg_count,
903 x_msg_data,
904 '_PVT'
905 );
906 WHEN OTHERS THEN
907 x_return_status := OKL_API.HANDLE_EXCEPTIONS
908 (
909 l_api_name,
910 G_PKG_NAME,
911 'OTHERS',
912 x_msg_count,
913 x_msg_data,
914 '_PVT'
915 );
916 END insert_row;
917
918 ---------------------------------------
919 -- PL/SQL TBL insert_row for:RPP_TBL --
920 ---------------------------------------
921 PROCEDURE insert_row(
922 p_api_version IN NUMBER,
923 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
924 x_return_status OUT NOCOPY VARCHAR2,
925 x_msg_count OUT NOCOPY NUMBER,
926 x_msg_data OUT NOCOPY VARCHAR2,
927 p_rpp_tbl IN rpp_tbl_type,
928 x_rpp_tbl OUT NOCOPY rpp_tbl_type) IS
929
930 l_api_version CONSTANT NUMBER := 1;
931 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
932 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
933 l_error_tbl OKL_API.ERROR_TBL_TYPE;
934 BEGIN
935 OKL_API.init_msg_list(p_init_msg_list);
936 -- Make sure PL/SQL table has records in it before passing
937 IF (p_rpp_tbl.COUNT > 0) THEN
938 insert_row (
939 p_api_version => p_api_version,
940 p_init_msg_list => OKL_API.G_FALSE,
941 x_return_status => x_return_status,
942 x_msg_count => x_msg_count,
943 x_msg_data => x_msg_data,
944 p_rpp_tbl => p_rpp_tbl,
945 x_rpp_tbl => x_rpp_tbl,
946 px_error_tbl => l_error_tbl);
947 END IF;
948 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
949 EXCEPTION
950 WHEN OKL_API.G_EXCEPTION_ERROR THEN
951 x_return_status := OKL_API.HANDLE_EXCEPTIONS
952 (
953 l_api_name,
954 G_PKG_NAME,
955 'OKL_API.G_RET_STS_ERROR',
956 x_msg_count,
957 x_msg_data,
958 '_PVT'
959 );
960 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
961 x_return_status := OKL_API.HANDLE_EXCEPTIONS
962 (
963 l_api_name,
964 G_PKG_NAME,
965 'OKL_API.G_RET_STS_UNEXP_ERROR',
966 x_msg_count,
967 x_msg_data,
968 '_PVT'
969 );
970 WHEN OTHERS THEN
971 x_return_status := OKL_API.HANDLE_EXCEPTIONS
972 (
973 l_api_name,
974 G_PKG_NAME,
975 'OTHERS',
976 x_msg_count,
977 x_msg_data,
978 '_PVT'
979 );
980 END insert_row;
981 ---------------------------------------------------------------------------
982 -- PROCEDURE lock_row
983 ---------------------------------------------------------------------------
984 ----------------------------------------
985 -- lock_row for:OKL_REPORT_PARAMETERS --
986 ----------------------------------------
987 PROCEDURE lock_row(
988 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
989 x_return_status OUT NOCOPY VARCHAR2,
990 x_msg_count OUT NOCOPY NUMBER,
991 x_msg_data OUT NOCOPY VARCHAR2,
992 p_rpp_rec IN rpp_rec_type) IS
993
994 E_Resource_Busy EXCEPTION;
995 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
996 CURSOR lock_csr (p_rpp_rec IN rpp_rec_type) IS
997 SELECT *
998 FROM OKL_REPORT_PARAMETERS
999 WHERE PARAMETER_ID = p_rpp_rec.parameter_id
1000 FOR UPDATE NOWAIT;
1001
1002 l_api_version CONSTANT NUMBER := 1;
1003 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1004 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1005 l_lock_var lock_csr%ROWTYPE;
1006 l_row_notfound BOOLEAN := FALSE;
1007 lc_row_notfound BOOLEAN := FALSE;
1008 BEGIN
1009 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1010 p_init_msg_list,
1011 '_PVT',
1012 x_return_status);
1013 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1014 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1015 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1016 RAISE OKL_API.G_EXCEPTION_ERROR;
1017 END IF;
1018 BEGIN
1019 OPEN lock_csr(p_rpp_rec);
1020 FETCH lock_csr INTO l_lock_var;
1021 l_row_notfound := lock_csr%NOTFOUND;
1022 CLOSE lock_csr;
1023 EXCEPTION
1024 WHEN E_Resource_Busy THEN
1025 IF (lock_csr%ISOPEN) THEN
1026 CLOSE lock_csr;
1027 END IF;
1028 OKL_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1029 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1030 END;
1031
1032 IF ( l_row_notfound ) THEN
1033 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1034 RAISE OKL_API.G_EXCEPTION_ERROR;
1035 ELSE
1036 IF (l_lock_var.parameter_id <> p_rpp_rec.parameter_id) THEN
1037 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1038 RAISE OKL_API.G_EXCEPTION_ERROR;
1039 END IF;
1040 END IF;
1041 x_return_status := l_return_status;
1042 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1043 EXCEPTION
1044 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1045 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1046 (
1047 l_api_name,
1048 G_PKG_NAME,
1049 'OKL_API.G_RET_STS_ERROR',
1050 x_msg_count,
1051 x_msg_data,
1052 '_PVT'
1053 );
1054 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1055 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1056 (
1057 l_api_name,
1058 G_PKG_NAME,
1059 'OKL_API.G_RET_STS_UNEXP_ERROR',
1060 x_msg_count,
1061 x_msg_data,
1062 '_PVT'
1063 );
1064 WHEN OTHERS THEN
1065 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1066 (
1067 l_api_name,
1068 G_PKG_NAME,
1069 'OTHERS',
1070 x_msg_count,
1071 x_msg_data,
1072 '_PVT'
1073 );
1074 END lock_row;
1075 -----------------------------------------
1076 -- lock_row for: OKL_REPORT_PARAMETERS --
1077 -----------------------------------------
1078 PROCEDURE lock_row(
1079 p_api_version IN NUMBER,
1080 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1081 x_return_status OUT NOCOPY VARCHAR2,
1082 x_msg_count OUT NOCOPY NUMBER,
1083 x_msg_data OUT NOCOPY VARCHAR2,
1084 p_rpp_rec IN rpp_rec_type) IS
1085
1086 l_api_version CONSTANT NUMBER := 1;
1087 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1088 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1089 l_rpp_rec rpp_rec_type:= p_rpp_rec;
1090 BEGIN
1091 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1092 G_PKG_NAME,
1093 p_init_msg_list,
1094 l_api_version,
1095 p_api_version,
1096 '_PVT',
1097 x_return_status);
1098 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1099 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1100 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1101 RAISE OKL_API.G_EXCEPTION_ERROR;
1102 END IF;
1103 -----------------------------------------
1104 -- Move VIEW record to "Child" records --
1105 -----------------------------------------
1106 ---------------------------------------------
1107 -- Call the LOCK_ROW for each child record --
1108 ---------------------------------------------
1109 lock_row(
1110 p_init_msg_list,
1111 l_return_status,
1112 x_msg_count,
1113 x_msg_data,
1114 l_rpp_rec
1115 );
1116 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1117 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1118 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1119 RAISE OKL_API.G_EXCEPTION_ERROR;
1120 END IF;
1121 x_return_status := l_return_status;
1122 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1123 EXCEPTION
1124 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1125 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1126 (
1127 l_api_name,
1128 G_PKG_NAME,
1129 'OKL_API.G_RET_STS_ERROR',
1130 x_msg_count,
1131 x_msg_data,
1132 '_PVT'
1133 );
1134 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1135 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1136 (
1137 l_api_name,
1138 G_PKG_NAME,
1139 'OKL_API.G_RET_STS_UNEXP_ERROR',
1140 x_msg_count,
1141 x_msg_data,
1142 '_PVT'
1143 );
1144 WHEN OTHERS THEN
1145 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1146 (
1147 l_api_name,
1148 G_PKG_NAME,
1149 'OTHERS',
1150 x_msg_count,
1151 x_msg_data,
1152 '_PVT'
1153 );
1154 END lock_row;
1155 -------------------------------------
1156 -- PL/SQL TBL lock_row for:RPP_TBL --
1157 -------------------------------------
1158 PROCEDURE lock_row(
1159 p_api_version IN NUMBER,
1160 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1161 x_return_status OUT NOCOPY VARCHAR2,
1162 x_msg_count OUT NOCOPY NUMBER,
1163 x_msg_data OUT NOCOPY VARCHAR2,
1164 p_rpp_tbl IN rpp_tbl_type,
1165 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1166
1167 l_api_version CONSTANT NUMBER := 1;
1168 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
1169 i NUMBER := 0;
1170 BEGIN
1171 OKL_API.init_msg_list(p_init_msg_list);
1172 -- Make sure PL/SQL table has recrods in it before passing
1173 IF (p_rpp_tbl.COUNT > 0) THEN
1174 i := p_rpp_tbl.FIRST;
1175 LOOP
1176 DECLARE
1177 l_error_rec OKL_API.ERROR_REC_TYPE;
1178 BEGIN
1179 l_error_rec.api_name := l_api_name;
1180 l_error_rec.api_package := G_PKG_NAME;
1181 l_error_rec.idx := i;
1182 lock_row(
1183 p_api_version => p_api_version,
1184 p_init_msg_list => OKL_API.G_FALSE,
1185 x_return_status => l_error_rec.error_type,
1186 x_msg_count => l_error_rec.msg_count,
1187 x_msg_data => l_error_rec.msg_data,
1188 p_rpp_rec => p_rpp_tbl(i));
1189 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
1190 l_error_rec.sqlcode := SQLCODE;
1191 load_error_tbl(l_error_rec, px_error_tbl);
1192 ELSE
1193 x_msg_count := l_error_rec.msg_count;
1194 x_msg_data := l_error_rec.msg_data;
1195 END IF;
1196 EXCEPTION
1197 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1198 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
1199 l_error_rec.sqlcode := SQLCODE;
1200 load_error_tbl(l_error_rec, px_error_tbl);
1201 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1202 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
1203 l_error_rec.sqlcode := SQLCODE;
1204 load_error_tbl(l_error_rec, px_error_tbl);
1205 WHEN OTHERS THEN
1206 l_error_rec.error_type := 'OTHERS';
1207 l_error_rec.sqlcode := SQLCODE;
1208 load_error_tbl(l_error_rec, px_error_tbl);
1209 END;
1210 EXIT WHEN (i = p_rpp_tbl.LAST);
1211 i := p_rpp_tbl.NEXT(i);
1212 END LOOP;
1213 END IF;
1214 -- Loop through the error_tbl to find the error with the highest severity
1215 -- and return it.
1216 x_return_status := find_highest_exception(px_error_tbl);
1217 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1218 EXCEPTION
1219 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1220 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1221 (
1222 l_api_name,
1223 G_PKG_NAME,
1224 'OKL_API.G_RET_STS_ERROR',
1225 x_msg_count,
1226 x_msg_data,
1227 '_PVT'
1228 );
1229 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1230 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1231 (
1232 l_api_name,
1233 G_PKG_NAME,
1234 'OKL_API.G_RET_STS_UNEXP_ERROR',
1235 x_msg_count,
1236 x_msg_data,
1237 '_PVT'
1238 );
1239 WHEN OTHERS THEN
1240 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1241 (
1242 l_api_name,
1243 G_PKG_NAME,
1244 'OTHERS',
1245 x_msg_count,
1246 x_msg_data,
1247 '_PVT'
1248 );
1249 END lock_row;
1250 -------------------------------------
1251 -- PL/SQL TBL lock_row for:RPP_TBL --
1252 -------------------------------------
1253 PROCEDURE lock_row(
1254 p_api_version IN NUMBER,
1255 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1256 x_return_status OUT NOCOPY VARCHAR2,
1257 x_msg_count OUT NOCOPY NUMBER,
1258 x_msg_data OUT NOCOPY VARCHAR2,
1259 p_rpp_tbl IN rpp_tbl_type) IS
1260
1261 l_api_version CONSTANT NUMBER := 1;
1262 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1263 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1264 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1265 BEGIN
1266 OKL_API.init_msg_list(p_init_msg_list);
1267 -- Make sure PL/SQL table has recrods in it before passing
1268 IF (p_rpp_tbl.COUNT > 0) THEN
1269 lock_row(
1270 p_api_version => p_api_version,
1271 p_init_msg_list => OKL_API.G_FALSE,
1272 x_return_status => x_return_status,
1273 x_msg_count => x_msg_count,
1274 x_msg_data => x_msg_data,
1275 p_rpp_tbl => p_rpp_tbl,
1276 px_error_tbl => l_error_tbl);
1277 END IF;
1278 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1279 EXCEPTION
1280 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1281 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1282 (
1283 l_api_name,
1284 G_PKG_NAME,
1285 'OKL_API.G_RET_STS_ERROR',
1286 x_msg_count,
1287 x_msg_data,
1288 '_PVT'
1289 );
1290 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1291 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1292 (
1293 l_api_name,
1294 G_PKG_NAME,
1295 'OKL_API.G_RET_STS_UNEXP_ERROR',
1296 x_msg_count,
1297 x_msg_data,
1298 '_PVT'
1299 );
1300 WHEN OTHERS THEN
1301 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1302 (
1303 l_api_name,
1304 G_PKG_NAME,
1305 'OTHERS',
1306 x_msg_count,
1307 x_msg_data,
1308 '_PVT'
1309 );
1310 END lock_row;
1311 ---------------------------------------------------------------------------
1312 -- PROCEDURE update_row
1313 ---------------------------------------------------------------------------
1314 ------------------------------------------
1315 -- update_row for:OKL_REPORT_PARAMETERS --
1316 ------------------------------------------
1317 PROCEDURE update_row(
1318 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1319 x_return_status OUT NOCOPY VARCHAR2,
1320 x_msg_count OUT NOCOPY NUMBER,
1321 x_msg_data OUT NOCOPY VARCHAR2,
1322 p_rpp_rec IN rpp_rec_type,
1323 x_rpp_rec OUT NOCOPY rpp_rec_type) IS
1324
1325 l_api_version CONSTANT NUMBER := 1;
1326 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
1327 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1328 l_rpp_rec rpp_rec_type := p_rpp_rec;
1329 l_def_rpp_rec rpp_rec_type;
1330 l_row_notfound BOOLEAN := TRUE;
1331 ----------------------------------
1332 -- FUNCTION populate_new_record --
1333 ----------------------------------
1334 FUNCTION populate_new_record (
1335 p_rpp_rec IN rpp_rec_type,
1336 x_rpp_rec OUT NOCOPY rpp_rec_type
1337 ) RETURN VARCHAR2 IS
1338 l_rpp_rec rpp_rec_type;
1339 l_row_notfound BOOLEAN := TRUE;
1340 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1341 BEGIN
1342 x_rpp_rec := p_rpp_rec;
1343 -- Get current database values
1344 l_rpp_rec := get_rec(p_rpp_rec, l_return_status);
1345 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
1346 IF x_rpp_rec.parameter_id IS NULL OR x_rpp_rec.parameter_id = OKL_API.G_MISS_NUM THEN
1347 x_rpp_rec.parameter_id := l_rpp_rec.parameter_id;
1348 END IF;
1349 IF x_rpp_rec.report_id IS NULL OR x_rpp_rec.report_id = OKL_API.G_MISS_NUM THEN
1350 x_rpp_rec.report_id := l_rpp_rec.report_id;
1351 END IF;
1352 IF x_rpp_rec.parameter_type IS NULL OR x_rpp_rec.parameter_type = OKL_API.G_MISS_CHAR THEN
1353 x_rpp_rec.parameter_type := l_rpp_rec.parameter_type;
1354 END IF;
1355 IF x_rpp_rec.param_num_value1 = OKL_API.G_MISS_NUM THEN
1356 x_rpp_rec.param_num_value1 := l_rpp_rec.param_num_value1;
1357 END IF;
1358 IF x_rpp_rec.param_char_value1 = OKL_API.G_MISS_CHAR THEN
1359 x_rpp_rec.param_char_value1 := l_rpp_rec.param_char_value1;
1360 END IF;
1361 IF x_rpp_rec.param_date_value1 = OKL_API.G_MISS_DATE THEN
1362 x_rpp_rec.param_date_value1 := l_rpp_rec.param_date_value1;
1363 END IF;
1364 IF x_rpp_rec.created_by IS NULL OR x_rpp_rec.created_by = OKL_API.G_MISS_NUM THEN
1365 x_rpp_rec.created_by := l_rpp_rec.created_by;
1366 END IF;
1367 IF x_rpp_rec.source_table = OKL_API.G_MISS_CHAR THEN
1368 x_rpp_rec.source_table := l_rpp_rec.source_table;
1369 END IF;
1370 IF x_rpp_rec.creation_date IS NULL OR x_rpp_rec.creation_date = OKL_API.G_MISS_DATE THEN
1371 x_rpp_rec.creation_date := l_rpp_rec.creation_date;
1372 END IF;
1373 IF x_rpp_rec.last_updated_by IS NULL OR x_rpp_rec.last_updated_by = OKL_API.G_MISS_NUM THEN
1374 x_rpp_rec.last_updated_by := l_rpp_rec.last_updated_by;
1375 END IF;
1376 IF x_rpp_rec.last_update_date IS NULL OR x_rpp_rec.last_update_date = OKL_API.G_MISS_DATE THEN
1377 x_rpp_rec.last_update_date := l_rpp_rec.last_update_date;
1378 END IF;
1379 IF x_rpp_rec.last_update_login = OKL_API.G_MISS_NUM THEN
1380 x_rpp_rec.last_update_login := l_rpp_rec.last_update_login;
1381 END IF;
1382 END IF;
1383 RETURN(l_return_status);
1384 END populate_new_record;
1385 ----------------------------------------------
1386 -- Set_Attributes for:OKL_REPORT_PARAMETERS --
1387 ----------------------------------------------
1388 FUNCTION Set_Attributes (
1389 p_rpp_rec IN rpp_rec_type,
1390 x_rpp_rec OUT NOCOPY rpp_rec_type
1391 ) RETURN VARCHAR2 IS
1392 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1393 BEGIN
1394 x_rpp_rec := p_rpp_rec;
1395 RETURN(l_return_status);
1396 END Set_Attributes;
1397 BEGIN
1398 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1399 p_init_msg_list,
1400 '_PVT',
1401 x_return_status);
1402 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1403 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1404 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1405 RAISE OKL_API.G_EXCEPTION_ERROR;
1406 END IF;
1407 --- Setting item attributes
1408 l_return_status := Set_Attributes(
1409 p_rpp_rec, -- IN
1410 l_rpp_rec); -- OUT
1411 --- If any errors happen abort API
1412 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1413 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1414 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1415 RAISE OKL_API.G_EXCEPTION_ERROR;
1416 END IF;
1417 l_return_status := populate_new_record(l_rpp_rec, l_def_rpp_rec);
1418 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1419 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1420 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1421 RAISE OKL_API.G_EXCEPTION_ERROR;
1422 END IF;
1423 UPDATE OKL_REPORT_PARAMETERS
1424 SET REPORT_ID = l_def_rpp_rec.report_id,
1425 PARAMETER_TYPE_CODE = l_def_rpp_rec.parameter_type,
1426 PARAM_NUM_VALUE1 = l_def_rpp_rec.param_num_value1,
1427 PARAM_CHAR_VALUE1 = l_def_rpp_rec.param_char_value1,
1428 PARAM_DATE_VALUE1 = l_def_rpp_rec.param_date_value1,
1429 CREATED_BY = l_def_rpp_rec.created_by,
1430 SOURCE_TABLE = l_def_rpp_rec.source_table,
1431 CREATION_DATE = l_def_rpp_rec.creation_date,
1432 LAST_UPDATED_BY = l_def_rpp_rec.last_updated_by,
1433 LAST_UPDATE_DATE = l_def_rpp_rec.last_update_date,
1434 LAST_UPDATE_LOGIN = l_def_rpp_rec.last_update_login
1435 WHERE PARAMETER_ID = l_def_rpp_rec.parameter_id;
1436
1437 x_rpp_rec := l_rpp_rec;
1438 x_return_status := l_return_status;
1439 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1440 EXCEPTION
1441 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1442 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1443 (
1444 l_api_name,
1445 G_PKG_NAME,
1446 'OKL_API.G_RET_STS_ERROR',
1447 x_msg_count,
1448 x_msg_data,
1449 '_PVT'
1450 );
1451 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1452 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1453 (
1454 l_api_name,
1455 G_PKG_NAME,
1456 'OKL_API.G_RET_STS_UNEXP_ERROR',
1457 x_msg_count,
1458 x_msg_data,
1459 '_PVT'
1460 );
1461 WHEN OTHERS THEN
1462 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1463 (
1464 l_api_name,
1465 G_PKG_NAME,
1466 'OTHERS',
1467 x_msg_count,
1468 x_msg_data,
1469 '_PVT'
1470 );
1471 END update_row;
1472 ------------------------------------------
1473 -- update_row for:OKL_REPORT_PARAMETERS --
1474 ------------------------------------------
1475 PROCEDURE update_row(
1476 p_api_version IN NUMBER,
1477 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1478 x_return_status OUT NOCOPY VARCHAR2,
1479 x_msg_count OUT NOCOPY NUMBER,
1480 x_msg_data OUT NOCOPY VARCHAR2,
1481 p_rpp_rec IN rpp_rec_type,
1482 x_rpp_rec OUT NOCOPY rpp_rec_type) IS
1483
1484 l_api_version CONSTANT NUMBER := 1;
1485 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1486 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1487 l_rpp_rec rpp_rec_type := p_rpp_rec;
1488 l_def_rpp_rec rpp_rec_type;
1489 l_db_rpp_rec rpp_rec_type;
1490 lx_rpp_rec rpp_rec_type;
1491 -------------------------------
1492 -- FUNCTION fill_who_columns --
1493 -------------------------------
1494 FUNCTION fill_who_columns (
1495 p_rpp_rec IN rpp_rec_type
1496 ) RETURN rpp_rec_type IS
1497 l_rpp_rec rpp_rec_type := p_rpp_rec;
1498 BEGIN
1499 l_rpp_rec.LAST_UPDATE_DATE := SYSDATE;
1500 l_rpp_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1501 l_rpp_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1502 RETURN(l_rpp_rec);
1503 END fill_who_columns;
1504 ----------------------------------
1505 -- FUNCTION populate_new_record --
1506 ----------------------------------
1507 FUNCTION populate_new_record (
1508 p_rpp_rec IN rpp_rec_type,
1509 x_rpp_rec OUT NOCOPY rpp_rec_type
1510 ) RETURN VARCHAR2 IS
1511 l_row_notfound BOOLEAN := TRUE;
1512 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1513 BEGIN
1514 x_rpp_rec := p_rpp_rec;
1515 -- Get current database values
1516 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
1517 -- so it may be verified through LOCK_ROW.
1518 l_db_rpp_rec := get_rec(p_rpp_rec, l_return_status);
1519 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
1520 IF x_rpp_rec.parameter_id IS NULL OR x_rpp_rec.parameter_id = OKL_API.G_MISS_NUM THEN
1521 x_rpp_rec.parameter_id := l_db_rpp_rec.parameter_id;
1522 END IF;
1523 IF x_rpp_rec.report_id IS NULL OR x_rpp_rec.report_id = OKL_API.G_MISS_NUM THEN
1524 x_rpp_rec.report_id := l_db_rpp_rec.report_id;
1525 END IF;
1526 IF x_rpp_rec.parameter_type IS NULL OR x_rpp_rec.parameter_type = OKL_API.G_MISS_CHAR THEN
1527 x_rpp_rec.parameter_type := l_db_rpp_rec.parameter_type;
1528 END IF;
1529 IF x_rpp_rec.param_num_value1 = OKL_API.G_MISS_NUM THEN
1530 x_rpp_rec.param_num_value1 := l_db_rpp_rec.param_num_value1;
1531 END IF;
1532 IF x_rpp_rec.param_char_value1 = OKL_API.G_MISS_CHAR THEN
1533 x_rpp_rec.param_char_value1 := l_db_rpp_rec.param_char_value1;
1534 END IF;
1535 IF x_rpp_rec.param_date_value1 = OKL_API.G_MISS_DATE THEN
1536 x_rpp_rec.param_date_value1 := l_db_rpp_rec.param_date_value1;
1537 END IF;
1538 IF x_rpp_rec.created_by IS NULL OR x_rpp_rec.created_by = OKL_API.G_MISS_NUM THEN
1539 x_rpp_rec.created_by := l_db_rpp_rec.created_by;
1540 END IF;
1541 IF x_rpp_rec.source_table = OKL_API.G_MISS_CHAR THEN
1542 x_rpp_rec.source_table := l_db_rpp_rec.source_table;
1543 END IF;
1544 IF x_rpp_rec.creation_date IS NULL OR x_rpp_rec.creation_date = OKL_API.G_MISS_DATE THEN
1545 x_rpp_rec.creation_date := l_db_rpp_rec.creation_date;
1546 END IF;
1547 IF x_rpp_rec.last_updated_by IS NULL OR x_rpp_rec.last_updated_by = OKL_API.G_MISS_NUM THEN
1548 x_rpp_rec.last_updated_by := l_db_rpp_rec.last_updated_by;
1549 END IF;
1550 IF x_rpp_rec.last_update_date IS NULL OR x_rpp_rec.last_update_date = OKL_API.G_MISS_DATE THEN
1551 x_rpp_rec.last_update_date := l_db_rpp_rec.last_update_date;
1552 END IF;
1553 IF x_rpp_rec.last_update_login = OKL_API.G_MISS_NUM THEN
1554 x_rpp_rec.last_update_login := l_db_rpp_rec.last_update_login;
1555 END IF;
1556 END IF;
1557 RETURN(l_return_status);
1558 END populate_new_record;
1559 ----------------------------------------------
1560 -- Set_Attributes for:OKL_REPORT_PARAMETERS --
1561 ----------------------------------------------
1562 FUNCTION Set_Attributes (
1563 p_rpp_rec IN rpp_rec_type,
1564 x_rpp_rec OUT NOCOPY rpp_rec_type
1565 ) RETURN VARCHAR2 IS
1566 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1567 BEGIN
1568 x_rpp_rec := p_rpp_rec;
1569 RETURN(l_return_status);
1570 END Set_Attributes;
1571 BEGIN
1572 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1573 G_PKG_NAME,
1574 p_init_msg_list,
1575 l_api_version,
1576 p_api_version,
1577 '_PVT',
1578 x_return_status);
1579 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1580 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1581 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1582 RAISE OKL_API.G_EXCEPTION_ERROR;
1583 END IF;
1584 --- Setting item attributes
1585 l_return_status := Set_Attributes(
1586 p_rpp_rec, -- IN
1587 x_rpp_rec); -- OUT
1588 --- If any errors happen abort API
1589 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1590 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1591 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1592 RAISE OKL_API.G_EXCEPTION_ERROR;
1593 END IF;
1594 l_return_status := populate_new_record(l_rpp_rec, l_def_rpp_rec);
1595 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1596 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1597 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1598 RAISE OKL_API.G_EXCEPTION_ERROR;
1599 END IF;
1600 l_def_rpp_rec := null_out_defaults(l_def_rpp_rec);
1601 l_def_rpp_rec := fill_who_columns(l_def_rpp_rec);
1602 --- Validate all non-missing attributes (Item Level Validation)
1603 l_return_status := Validate_Attributes(l_def_rpp_rec);
1604 --- If any errors happen abort API
1605 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1606 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1607 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1608 RAISE OKL_API.G_EXCEPTION_ERROR;
1609 END IF;
1610 l_return_status := Validate_Record(l_def_rpp_rec, l_db_rpp_rec);
1611 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1612 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1613 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1614 RAISE OKL_API.G_EXCEPTION_ERROR;
1615 END IF;
1616
1617 -- Lock the Record
1618 lock_row(
1619 p_api_version => p_api_version,
1620 p_init_msg_list => p_init_msg_list,
1621 x_return_status => l_return_status,
1622 x_msg_count => x_msg_count,
1623 x_msg_data => x_msg_data,
1624 p_rpp_rec => p_rpp_rec);
1625 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1626 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1627 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1628 RAISE OKL_API.G_EXCEPTION_ERROR;
1629 END IF;
1630
1631 -----------------------------------------
1632 -- Move VIEW record to "Child" records --
1633 -----------------------------------------
1634 -----------------------------------------------
1635 -- Call the UPDATE_ROW for each child record --
1636 -----------------------------------------------
1637 update_row(
1638 p_init_msg_list,
1639 l_return_status,
1640 x_msg_count,
1641 x_msg_data,
1642 l_rpp_rec,
1643 lx_rpp_rec
1644 );
1645 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1646 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1647 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1648 RAISE OKL_API.G_EXCEPTION_ERROR;
1649 END IF;
1650 x_rpp_rec := l_def_rpp_rec;
1651 x_return_status := l_return_status;
1652 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1653 EXCEPTION
1654 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1655 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1656 (
1657 l_api_name,
1658 G_PKG_NAME,
1659 'OKL_API.G_RET_STS_ERROR',
1660 x_msg_count,
1661 x_msg_data,
1662 '_PVT'
1663 );
1664 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1665 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1666 (
1667 l_api_name,
1668 G_PKG_NAME,
1669 'OKL_API.G_RET_STS_UNEXP_ERROR',
1670 x_msg_count,
1671 x_msg_data,
1672 '_PVT'
1673 );
1674 WHEN OTHERS THEN
1675 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1676 (
1677 l_api_name,
1678 G_PKG_NAME,
1679 'OTHERS',
1680 x_msg_count,
1681 x_msg_data,
1682 '_PVT'
1683 );
1684 END update_row;
1685 ---------------------------------------
1686 -- PL/SQL TBL update_row for:rpp_tbl --
1687 ---------------------------------------
1688 PROCEDURE update_row(
1689 p_api_version IN NUMBER,
1690 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1691 x_return_status OUT NOCOPY VARCHAR2,
1692 x_msg_count OUT NOCOPY NUMBER,
1693 x_msg_data OUT NOCOPY VARCHAR2,
1694 p_rpp_tbl IN rpp_tbl_type,
1695 x_rpp_tbl OUT NOCOPY rpp_tbl_type,
1696 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1697
1698 l_api_version CONSTANT NUMBER := 1;
1699 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
1700 i NUMBER := 0;
1701 BEGIN
1702 OKL_API.init_msg_list(p_init_msg_list);
1703 -- Make sure PL/SQL table has records in it before passing
1704 IF (p_rpp_tbl.COUNT > 0) THEN
1705 i := p_rpp_tbl.FIRST;
1706 LOOP
1707 DECLARE
1708 l_error_rec OKL_API.ERROR_REC_TYPE;
1709 BEGIN
1710 l_error_rec.api_name := l_api_name;
1711 l_error_rec.api_package := G_PKG_NAME;
1712 l_error_rec.idx := i;
1713 update_row (
1714 p_api_version => p_api_version,
1715 p_init_msg_list => OKL_API.G_FALSE,
1716 x_return_status => l_error_rec.error_type,
1717 x_msg_count => l_error_rec.msg_count,
1718 x_msg_data => l_error_rec.msg_data,
1719 p_rpp_rec => p_rpp_tbl(i),
1720 x_rpp_rec => x_rpp_tbl(i));
1721 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
1722 l_error_rec.sqlcode := SQLCODE;
1723 load_error_tbl(l_error_rec, px_error_tbl);
1724 ELSE
1725 x_msg_count := l_error_rec.msg_count;
1726 x_msg_data := l_error_rec.msg_data;
1727 END IF;
1728 EXCEPTION
1729 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1730 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
1731 l_error_rec.sqlcode := SQLCODE;
1732 load_error_tbl(l_error_rec, px_error_tbl);
1733 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1734 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
1735 l_error_rec.sqlcode := SQLCODE;
1736 load_error_tbl(l_error_rec, px_error_tbl);
1737 WHEN OTHERS THEN
1738 l_error_rec.error_type := 'OTHERS';
1739 l_error_rec.sqlcode := SQLCODE;
1740 load_error_tbl(l_error_rec, px_error_tbl);
1741 END;
1742 EXIT WHEN (i = p_rpp_tbl.LAST);
1743 i := p_rpp_tbl.NEXT(i);
1744 END LOOP;
1745 END IF;
1746 -- Loop through the error_tbl to find the error with the highest severity
1747 -- and return it.
1748 x_return_status := find_highest_exception(px_error_tbl);
1749 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1750 EXCEPTION
1751 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1752 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1753 (
1754 l_api_name,
1755 G_PKG_NAME,
1756 'OKL_API.G_RET_STS_ERROR',
1757 x_msg_count,
1758 x_msg_data,
1759 '_PVT'
1760 );
1761 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1762 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1763 (
1764 l_api_name,
1765 G_PKG_NAME,
1766 'OKL_API.G_RET_STS_UNEXP_ERROR',
1767 x_msg_count,
1768 x_msg_data,
1769 '_PVT'
1770 );
1771 WHEN OTHERS THEN
1772 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1773 (
1774 l_api_name,
1775 G_PKG_NAME,
1776 'OTHERS',
1777 x_msg_count,
1778 x_msg_data,
1779 '_PVT'
1780 );
1781 END update_row;
1782
1783 ---------------------------------------
1784 -- PL/SQL TBL update_row for:RPP_TBL --
1785 ---------------------------------------
1786 PROCEDURE update_row(
1787 p_api_version IN NUMBER,
1788 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1789 x_return_status OUT NOCOPY VARCHAR2,
1790 x_msg_count OUT NOCOPY NUMBER,
1791 x_msg_data OUT NOCOPY VARCHAR2,
1792 p_rpp_tbl IN rpp_tbl_type,
1793 x_rpp_tbl OUT NOCOPY rpp_tbl_type) IS
1794
1795 l_api_version CONSTANT NUMBER := 1;
1796 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
1797 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1798 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1799 BEGIN
1800 OKL_API.init_msg_list(p_init_msg_list);
1801 -- Make sure PL/SQL table has records in it before passing
1802 IF (p_rpp_tbl.COUNT > 0) THEN
1803 update_row (
1804 p_api_version => p_api_version,
1805 p_init_msg_list => OKL_API.G_FALSE,
1806 x_return_status => x_return_status,
1807 x_msg_count => x_msg_count,
1808 x_msg_data => x_msg_data,
1809 p_rpp_tbl => p_rpp_tbl,
1810 x_rpp_tbl => x_rpp_tbl,
1811 px_error_tbl => l_error_tbl);
1812 END IF;
1813 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1814 EXCEPTION
1815 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1816 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1817 (
1818 l_api_name,
1819 G_PKG_NAME,
1820 'OKL_API.G_RET_STS_ERROR',
1821 x_msg_count,
1822 x_msg_data,
1823 '_PVT'
1824 );
1825 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1826 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1827 (
1828 l_api_name,
1829 G_PKG_NAME,
1830 'OKL_API.G_RET_STS_UNEXP_ERROR',
1831 x_msg_count,
1832 x_msg_data,
1833 '_PVT'
1834 );
1835 WHEN OTHERS THEN
1836 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1837 (
1838 l_api_name,
1839 G_PKG_NAME,
1840 'OTHERS',
1841 x_msg_count,
1842 x_msg_data,
1843 '_PVT'
1844 );
1845 END update_row;
1846
1847 ---------------------------------------------------------------------------
1848 -- PROCEDURE delete_row
1849 ---------------------------------------------------------------------------
1850 ------------------------------------------
1851 -- delete_row for:OKL_REPORT_PARAMETERS --
1852 ------------------------------------------
1853 PROCEDURE delete_row(
1854 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1855 x_return_status OUT NOCOPY VARCHAR2,
1856 x_msg_count OUT NOCOPY NUMBER,
1857 x_msg_data OUT NOCOPY VARCHAR2,
1858 p_rpp_rec IN rpp_rec_type) IS
1859
1860 l_api_version CONSTANT NUMBER := 1;
1861 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
1862 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1863 l_rpp_rec rpp_rec_type := p_rpp_rec;
1864 l_row_notfound BOOLEAN := TRUE;
1865 BEGIN
1866 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1867 p_init_msg_list,
1868 '_PVT',
1869 x_return_status);
1870 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1871 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1872 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1873 RAISE OKL_API.G_EXCEPTION_ERROR;
1874 END IF;
1875
1876 DELETE FROM OKL_REPORT_PARAMETERS
1877 WHERE PARAMETER_ID = p_rpp_rec.parameter_id;
1878
1879 x_return_status := l_return_status;
1880 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1881 EXCEPTION
1882 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1883 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1884 (
1885 l_api_name,
1886 G_PKG_NAME,
1887 'OKL_API.G_RET_STS_ERROR',
1888 x_msg_count,
1889 x_msg_data,
1890 '_PVT'
1891 );
1892 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1893 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1894 (
1895 l_api_name,
1896 G_PKG_NAME,
1897 'OKL_API.G_RET_STS_UNEXP_ERROR',
1898 x_msg_count,
1899 x_msg_data,
1900 '_PVT'
1901 );
1902 WHEN OTHERS THEN
1903 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1904 (
1905 l_api_name,
1906 G_PKG_NAME,
1907 'OTHERS',
1908 x_msg_count,
1909 x_msg_data,
1910 '_PVT'
1911 );
1912 END delete_row;
1913 ------------------------------------------
1914 -- delete_row for:OKL_REPORT_PARAMETERS --
1915 ------------------------------------------
1916 PROCEDURE delete_row(
1917 p_api_version IN NUMBER,
1918 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1919 x_return_status OUT NOCOPY VARCHAR2,
1920 x_msg_count OUT NOCOPY NUMBER,
1921 x_msg_data OUT NOCOPY VARCHAR2,
1922 p_rpp_rec IN rpp_rec_type) IS
1923
1924 l_api_version CONSTANT NUMBER := 1;
1925 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
1926 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1927 l_rpp_rec rpp_rec_type := p_rpp_rec;
1928 BEGIN
1929 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1930 G_PKG_NAME,
1931 p_init_msg_list,
1932 l_api_version,
1933 p_api_version,
1934 '_PVT',
1935 x_return_status);
1936 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1937 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1938 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1939 RAISE OKL_API.G_EXCEPTION_ERROR;
1940 END IF;
1941 -----------------------------------------
1942 -- Move VIEW record to "Child" records --
1943 -----------------------------------------
1944 -----------------------------------------------
1945 -- Call the DELETE_ROW for each child record --
1946 -----------------------------------------------
1947 delete_row(
1948 p_init_msg_list,
1949 l_return_status,
1950 x_msg_count,
1951 x_msg_data,
1952 l_rpp_rec
1953 );
1954 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1955 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1956 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1957 RAISE OKL_API.G_EXCEPTION_ERROR;
1958 END IF;
1959 x_return_status := l_return_status;
1960 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1961 EXCEPTION
1962 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1963 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1964 (
1965 l_api_name,
1966 G_PKG_NAME,
1967 'OKL_API.G_RET_STS_ERROR',
1968 x_msg_count,
1969 x_msg_data,
1970 '_PVT'
1971 );
1972 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1973 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1974 (
1975 l_api_name,
1976 G_PKG_NAME,
1977 'OKL_API.G_RET_STS_UNEXP_ERROR',
1978 x_msg_count,
1979 x_msg_data,
1980 '_PVT'
1981 );
1982 WHEN OTHERS THEN
1983 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1984 (
1985 l_api_name,
1986 G_PKG_NAME,
1987 'OTHERS',
1988 x_msg_count,
1989 x_msg_data,
1990 '_PVT'
1991 );
1992 END delete_row;
1993 -----------------------------------------------------
1994 -- PL/SQL TBL delete_row for:OKL_REPORT_PARAMETERS --
1995 -----------------------------------------------------
1996 PROCEDURE delete_row(
1997 p_api_version IN NUMBER,
1998 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1999 x_return_status OUT NOCOPY VARCHAR2,
2000 x_msg_count OUT NOCOPY NUMBER,
2001 x_msg_data OUT NOCOPY VARCHAR2,
2002 p_rpp_tbl IN rpp_tbl_type,
2003 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2004
2005 l_api_version CONSTANT NUMBER := 1;
2006 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
2007 i NUMBER := 0;
2008 BEGIN
2009 OKL_API.init_msg_list(p_init_msg_list);
2010 -- Make sure PL/SQL table has records in it before passing
2011 IF (p_rpp_tbl.COUNT > 0) THEN
2012 i := p_rpp_tbl.FIRST;
2013 LOOP
2014 DECLARE
2015 l_error_rec OKL_API.ERROR_REC_TYPE;
2016 BEGIN
2017 l_error_rec.api_name := l_api_name;
2018 l_error_rec.api_package := G_PKG_NAME;
2019 l_error_rec.idx := i;
2020 delete_row (
2021 p_api_version => p_api_version,
2022 p_init_msg_list => OKL_API.G_FALSE,
2023 x_return_status => l_error_rec.error_type,
2024 x_msg_count => l_error_rec.msg_count,
2025 x_msg_data => l_error_rec.msg_data,
2026 p_rpp_rec => p_rpp_tbl(i));
2027 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
2028 l_error_rec.sqlcode := SQLCODE;
2029 load_error_tbl(l_error_rec, px_error_tbl);
2030 ELSE
2031 x_msg_count := l_error_rec.msg_count;
2032 x_msg_data := l_error_rec.msg_data;
2033 END IF;
2034 EXCEPTION
2035 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2036 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
2037 l_error_rec.sqlcode := SQLCODE;
2038 load_error_tbl(l_error_rec, px_error_tbl);
2039 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2040 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
2041 l_error_rec.sqlcode := SQLCODE;
2042 load_error_tbl(l_error_rec, px_error_tbl);
2043 WHEN OTHERS THEN
2044 l_error_rec.error_type := 'OTHERS';
2045 l_error_rec.sqlcode := SQLCODE;
2046 load_error_tbl(l_error_rec, px_error_tbl);
2047 END;
2048 EXIT WHEN (i = p_rpp_tbl.LAST);
2049 i := p_rpp_tbl.NEXT(i);
2050 END LOOP;
2051 END IF;
2052 -- Loop through the error_tbl to find the error with the highest severity
2053 -- and return it.
2054 x_return_status := find_highest_exception(px_error_tbl);
2055 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2056 EXCEPTION
2057 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2058 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2059 (
2060 l_api_name,
2061 G_PKG_NAME,
2062 'OKL_API.G_RET_STS_ERROR',
2063 x_msg_count,
2064 x_msg_data,
2065 '_PVT'
2066 );
2067 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2068 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2069 (
2070 l_api_name,
2071 G_PKG_NAME,
2072 'OKL_API.G_RET_STS_UNEXP_ERROR',
2073 x_msg_count,
2074 x_msg_data,
2075 '_PVT'
2076 );
2077 WHEN OTHERS THEN
2078 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2079 (
2080 l_api_name,
2081 G_PKG_NAME,
2082 'OTHERS',
2083 x_msg_count,
2084 x_msg_data,
2085 '_PVT'
2086 );
2087 END delete_row;
2088
2089 -----------------------------------------------------
2090 -- PL/SQL TBL delete_row for:OKL_REPORT_PARAMETERS --
2091 -----------------------------------------------------
2092 PROCEDURE delete_row(
2093 p_api_version IN NUMBER,
2094 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2095 x_return_status OUT NOCOPY VARCHAR2,
2096 x_msg_count OUT NOCOPY NUMBER,
2097 x_msg_data OUT NOCOPY VARCHAR2,
2098 p_rpp_tbl IN rpp_tbl_type) IS
2099
2100 l_api_version CONSTANT NUMBER := 1;
2101 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2102 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2103 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2104 BEGIN
2105 OKL_API.init_msg_list(p_init_msg_list);
2106 -- Make sure PL/SQL table has records in it before passing
2107 IF (p_rpp_tbl.COUNT > 0) THEN
2108 delete_row (
2109 p_api_version => p_api_version,
2110 p_init_msg_list => OKL_API.G_FALSE,
2111 x_return_status => x_return_status,
2112 x_msg_count => x_msg_count,
2113 x_msg_data => x_msg_data,
2114 p_rpp_tbl => p_rpp_tbl,
2115 px_error_tbl => l_error_tbl);
2116 END IF;
2117 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2118 EXCEPTION
2119 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2120 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2121 (
2122 l_api_name,
2123 G_PKG_NAME,
2124 'OKL_API.G_RET_STS_ERROR',
2125 x_msg_count,
2126 x_msg_data,
2127 '_PVT'
2128 );
2129 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2130 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2131 (
2132 l_api_name,
2133 G_PKG_NAME,
2134 'OKL_API.G_RET_STS_UNEXP_ERROR',
2135 x_msg_count,
2136 x_msg_data,
2137 '_PVT'
2138 );
2139 WHEN OTHERS THEN
2140 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2141 (
2142 l_api_name,
2143 G_PKG_NAME,
2144 'OTHERS',
2145 x_msg_count,
2146 x_msg_data,
2147 '_PVT'
2148 );
2149 END delete_row;
2150
2151 END OKL_RPP_PVT;