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